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 +
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:
Convert(varchar(32), floor(TotalDuration/ 60)) + ‘:’ +
right(‘0’ + convert(varchar(2), TotalDuration % 60), 2)
select SUM(Convert(int, left(MyDurations, 2)) * 60 +
Convert(int, right(MyDurations,2))) as TotalDuration
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>