Sum up Time Field in SQL Server

Yesterday, one of a member Murali Krishna Rayudu in a Facebook Group MSBI Query Cracker's post the requirement to calculate Total Working Hours. In the scenario there was a column with Time Data Type and had the values for hours worked and the requirement was to sum up the time to calculate Total Hours worked. At first go, it looks straight forward as we have aggregate function  SUM to sum up column values, but it's not because sum of a column with TIME data type is not supported in SQL Server

NOTE: Time Data Type was introduced in SQL Server 2008

Let's try to sum up the values of a column with Time Data Type with SUM

declare @tab table(col time)
insert into @tab values
('01:15:23'),('12:15:41'),('15:45:12')
select SUM(col) from @tab

Look, we get the above error. So, it is concluded that aggregate functions not work on Time Data Type in SQL Server.

Solution to above I provided the following script to calculate the same on the post itself and thought of sharing the same on by blog. In the below script I have provided two ways.

declare @tab table(col time)
insert into @tab values
('01:15:23'),('12:15:41'),('15:45:12')

--Solution-1
select right('0'+cast(sum(DATEPART(hh,col)) + (sum(DATEPART(mi,col)) + (sum(DATEPART(S,col))/60))/60 as varchar),2)
       +':'+right('0'+ cast((sum(DATEPART(mi,col)) + (sum(DATEPART(S,col))/60))%60 as varchar),2)
       +':'+ right('0'+ cast(sum(DATEPART(s,col))%60 as varchar),2)
from @tab

--Solution-2
select cast(secs/3600 as varchar)
       +':'+cast((secs-(secs/3600)*3600)/60 as varchar)
       +':'+cast(secs%60 as varchar)
from( 
   select sum((DATEPART(HH,col)*3600)+(DATEPART(mi,col)*60)+DATEPART(S,col)) secs
   from @tab
) t