VS 2005 SQL Reports – Need to convert 5 character string to datatime

15 pts.
Tags:
SQL
SQL Server
SSRS
Visual Studio 2005
VS 2005
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Mariodlg
    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.
    2,790 pointsBadges:
    report
  • Haxatron
    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!
    15 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Haxatron
    [...] VS 2005 SQL Reports - Need to convert 5 character string to datatime [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following