VS 2005 SQL Reports – Need to convert 5 character string to datatime
Hey there. I am relatively new to SQL and have a problem. I am building a report that gets a summation of a column called 'Duration'. The datatype is char(5) e.g. "00:30" to represent a half hour. I suppose I need to change the datatype to sum it but can't figure out how to. What would my query look like and is there anything I am overlooking? Thank you so much for your time!
Software/Hardware used:
VS 2005, MS Reporting Services, SQL
Software/Hardware used:
VS 2005, MS Reporting Services, SQL
ASKED:
July 15, 2011 8:16 PM
UPDATED:
March 31, 2012 9:18 PM
Answer Wiki:
First off - you should study the CAST and CONVERT functions - you will get lots of use out of these, and should understand how they work.
If you just need to Sum the column to get a total of hours (or hours + minutes), the easiest way is to convert the string to a number.
If you want to avoid having to deal with rounding errors, I would convert it to minutes and sum the minutes, then convert back to hours/minutes at the end.
To convert a 5-character string of the form "HH:MM" to an integer number of minutes, use
<pre>Convert(int, left(stringname, 2)) * 60 +
Convert(int, right(stringname,2))</pre>
When you want to convert back to an HHH:MM kind of display, just reverse the conversion - hours would be your total divided by 60, and minutes would be the total modulo 60. One thing to watch out for - the minutes could be 0-9, which would be a single digit, so to look nice, you might have to prefix the minutes with "0". The easy trick to do this is to prefix the answer with "0", then take the rightmost 2 characters, e.g.:
Result string =
<pre>Convert(varchar(32), floor(total / 60)) + ':' +
right('0' + convert(varchar(2), total % 60), 2)</pre>
That may look tricky, but the left part just divides the total by 60, then takes the "floor" function (to get the integer part), then converts it to a variable-length character string.
The right part takes the total modulo 60 ("total % 60"), converts that to a variable-length string of 1-2 characters, prepends a "0" character, then returns the rightmost two characters.
One last hint for simplifying. When doing fairly complicated conversions, try using a nested query, rather than mixing a lot of conversions in one statement.
So - if you were querying a table MyDataTable and the column of interest was MyDurations, a query to get the total in the format of HHH:MM could be:
<pre>select
Convert(varchar(32), floor(TotalDuration/ 60)) + ':' +
right('0' + convert(varchar(2), TotalDuration % 60), 2)
from (
select SUM(Convert(int, left(MyDurations, 2)) * 60 +
Convert(int, right(MyDurations,2))) as TotalDuration
from MyDataTable
where ... -- some conditions
) tmp -- this is a temporary in-line table in the query
-- the name "tmp" is just for example, it can be "t" or
-- any name you like</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Try to use CAST function, example:
SELECT CAST(’16:22:11′ AS smalldatetime)
SQL Server will use “1900-01-01″ as base date, so the result for the previous example will be “1900-01-01 16:22:11″ of datetime type.
Read about CAST and CONVERT functions here.
Hope it helps.
Thanks! I ended up doing it sort of the way you suggested. I never could get CAST or CONVERT working. And it turns out I didn’t need to convert it since it looks like Reporting Services will do arithmetic on strings as long as it is only number characters. My main code looked like…
SELECT addedbyid, addedby, duration,servicedatetime,dateadded,
substring(duration,1,2) hours, substring(duration,4,2) as minutes,*
And the reporting services portion looked like…
=sum((Fields!hours.Value * 60 + Fields!minutes.Value) / 60)
If you have any closing thoughts or critiques I always love to be shown a better way. Thanks again for all your help!
CAST and CONVERT are extremely useful functions. You should become familiar with these if you are working in SQL Server.
I tend to use CONVERT – the syntax is simply:
CONVERT( format to which to convert , source data value )
Some example conversions:
CONVERT( int , “123.456″ )
CONVERT( varchar(32) , 99.999 )
Note that when converting to a data type that can have different lengths (like VARCHAR or CHAR), you need to specify the length.
CONVERT( varchar , 99.999 ) – this will throw an error
CAST is essentially the same as CONVERT, but the argument syntax is reversed, and you must include “AS” between the arguments instead of a comma.
CAST ( -an existing value- AS -a new datatype- )
CAST ( 123 AS VARCHAR(32) )
Google “SQL CAST” and open the MSDN link to the CAST and CONVERT functions for the documentation on the various conversion options and examples.