How To Sum Times From Time Datatype Columns In Hh:mm:ss?

I have a SQL Server table with 4 columns (TIME(0) datatype). Each row correspond to a day so I have: Col Time 1 -------- Col Time 2 ------ Col Time 3 '03:23:12' -------- '21:33:04

Solution 1:

At first, your final value should be 48:22:19 instead of 48:54:46. Secondly, you can't use TIME type to receive the result you want, because element range for hh is two digits, ranging from 0 to 23, that represent the hour( More information ).

Here comes a workaround, when at first you SUMTIME column converting each value in seconds and then find separately hours, minutes and seconds :

SELECTCAST(t.time_sum/3600ASVARCHAR(2)) +':'+CAST(t.time_sum%3600/60ASVARCHAR(2)) +':'+CAST(((t.time_sum%3600)%60) ASVARCHAR(2))
FROM ( SELECTSUM(DATEDIFF(S, '00:00:00', col1)) AS time_sum
       FROM tbl) t

SQLFiddle

Solution 2:

I found much easier way to do this..

SEC_TO_TIME(SUM(TIME_TO_SEC(column_name)))