How to Sum Up Time Field 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

Is it possible to sum up time field in SQL Server?

SQL Server does not support direct addition on time values; however, you can use datediff against 0 to find how many (of some interval - minutes, seconds, etc; choice depends on your required precision) to calculate the duration, and then dateadd the same:

declare @start time= '14:30:00', @duration time = '02:45:00';

declare @end time = dateadd(second, datediff(second, 0, @duration), @start);

select @end; -- 17:15:00.0000000

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

How to sum times from Time datatype columns in hh:mm:ss?

At first, your final value should be 48:22:19 instead of 48:54:46.

Secondly, you can't use TIME type to receive the result you want, because element range for hh is two digits, ranging from 0 to 23, that represent the hour( More information ).

Here comes a workaround, when at first you SUM TIME column converting each value in seconds and then find separately hours, minutes and seconds :

SELECT CAST(t.time_sum/3600 AS VARCHAR(2)) + ':'
     + CAST(t.time_sum%3600/60 AS VARCHAR(2)) + ':'
     + CAST(((t.time_sum%3600)%60) AS VARCHAR(2))
FROM ( SELECT SUM(DATEDIFF(S, '00:00:00', col1)) AS time_sum
       FROM tbl) t

SQLFiddle

How to sum a Time column (with millisecond) in SQL Server

To give it in milliseconds

SELECT 
    ID
    ,SUM(datediff(ms,cast('0:0' as time),TEMPO)) AS RESULT
FROM #TEMP_USER
GROUP BY ID;

or for formatted over 24 hours, you could try

CREATE TABLE #TEMP_USER(
        ID INT,
        TEMPO TIME(7)
    )

INSERT INTO #TEMP_USER 
VALUES
 (1, '01:59:37.5100000')
,(1, '23:00:00.0010000');



SELECT 
        ID
        ,datediff(ms,cast('0:0' as time),tempo) AS RESULT
    FROM #TEMP_USER

    SELECT cast(IQ.RESULT / (60* 60 *  1000) as nvarchar(8)) + ':' + REVERSE(CAST(REVERSE(cast(dateadd(ms,RESULT % 3600000,cast('0:0' as time)) as nvarchar(40))) as nvarchar(13)))
    FROM
    (
    SELECT 
        ID
        ,SUM(datediff(ms,cast('0:0' as time),TEMPO)) AS RESULT
    FROM #TEMP_USER
    GROUP BY ID
    ) IQ;



drop table #TEMP_USER ;

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

TSQL - Sum on time column with condition

Use LEAD function to get time in the next row and to calculate time interval. Then just group result by [bit]

WITH t AS(
SELECT
    [time],
    DATEDIFF(minute, [time], LEAD([time], 1, null) OVER (ORDER BY [time])) AS interval,
    [bit],
    [value]
FROM table1)
SELECT [bit], CAST(DATEADD(MINUTE, SUM(interval), '00:00') AS TIME), SUM([value]) FROM t
GROUP BY [bit]

---

Related Topics