Feb 18, 2016

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
select SUM(col) from @tab
Error with SUM for Time datatype

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

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

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

    Choose :
  • OR
  • To comment
No comments:
Write Comments