Sum Total Time in SQL Server

How to sum up time field in SQL Server

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?

SUM total time in SQL Server

if the time in hh/mm/ss then ::

SELECT studentid,studentname,
         DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', mytime)), '00:00:00.000') as time
    FROM
         worknote

How to Sum (Time(n)) in Sql Server?

Convert the time to an integer value before you sum it (for example, seconds):

SELECT SUM(
        datediff(second, '00:00:00', [TimeCol])
    )
FROM
    ...

Replace [TimeCol] with the name of the Time(n) column. This gives you the total time in seconds, which you can then easily convert to minutes, hours, etc...

Get the total sum hours in a column SQL SERVER

Your table schema hour is varchar, you need to cast as time, then do the calculation

SELECT  datediff(hour,min(cast(hour as time)),max(cast(hour as time)))
FROM Timetable

sqlfiddle

NOTE

I would suggest your hour column as datetime or time instead of varchar. because hour column intention is time.

---

EDIT

If your time is 9:00 to 17:30, you can try to use datediff minute to get the total diff minutes then divide 60 to get hours.

SELECT datediff(minute,min(cast(hour as time)),max(cast(hour as time))) / CAST(60 as float)
FROM Timetable

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6e005cdfad4eca3ff7c4c92ef14cc9c7

How to get the sum of hour, minutes, and seconds in 2 columns?

As mentioned in comments - if you're storing time values, you should really be using the TIME datatype - not a VARCHAR ..... always use the most appropriate datatype - no exceptions.

Anyway - since you have VARCHAR, the statement get a bit convoluted - but here you go:

SELECT 
    DATEADD(SECOND, DATEDIFF(SECOND, 0, CAST(NewTime AS TIME)), CAST(OldTime AS TIME)) 
FROM 
    dbo.YourTableNameHere

which would look a lot easier with TIME columns, of course:

SELECT 
    DATEADD(SECOND, DATEDIFF(SECOND, 0, NewTime), OldTime) 
FROM 
    dbo.YourTableNameHere

---

Related Topics