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
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,