How to sum up time field in SQL Server

Question

I have a column called "WrkHrs" and the data type is time(hh:mm:ss). I want to sum up the working hours for employees. But since it's time data type sql server doesn't let me use like sum(columnname).

How can I sum up the time data type fieled in sql query?

Answer 1

SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs))  FROM dbo.table  -- WHERE ... GROUP BY EmployeeID; 

You can format it pretty on the front end. Or in T-SQL:

;WITH w(e, mw) AS (     SELECT EmployeeID, SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs))      FROM dbo.table      -- WHERE ...     GROUP BY EmployeeID ) SELECT EmployeeID = e,   WrkHrs = RTRIM(mw/60) + ':' + RIGHT('0' + RTRIM(mw%60),2)   FROM w; 

However, you're using the wrong data type. TIME is used to indicate a point in time, not an interval or duration. Wouldn't it make sense to store their work hours in two distinct columns, StartTime and EndTime?

Answer 2

In order to sum up the working hours for an employee you can calculate the difference between the shift start time and end time in minutes and convert it to readable format as following:

    DECLARE @StartTime      datetime = '08:00'     DECLARE @EndTime        datetime = '10:47'     DECLARE @durMinutes     int     DECLARE @duration       nvarchar(5)      SET @durMinutes = DATEDIFF(MINUTE, @StartTime, @EndTime)      SET @duration =      (SELECT RIGHT('00' + CAST((@durMinutes / 60) AS VARCHAR(2)),2) + ':' +              RIGHT('00' + CAST((@durMinutes % 60) AS VARCHAR(2)), 2))      SELECT @duration 

The result : 02:47 two hours and 47 minutes