Add time column in SQL SERVER

Question

I got a column like team in a table. With records such as:

00:02:15
00:09:47
00:00:25
...

I need in mine select, find the sum of those minutes in format team. Return example: 00:12:27

I tried in many ways without success.

Answer 1

You can do it this way:

create table t1 (id int, t time)

insert into t1 values (1,'00:00:25.0000000')
insert into t1 values (1,'00:12:20.0000000')

insert into t1 values (2,'00:00:02.0000000')
insert into t1 values (2,'00:00:10.0000000')

SELECT ID, CAST(DATEADD(MILLISECOND,SUM(DATEDIFF
(MILLISECOND,0,CAST(T AS DATETIME))),0) AS TIME) FROM t1
GROUP BY ID

See working on Sqlfiddle

Answer 2

You can use a combination of DATEADD and DATEDIFF to calculate the difference in interval in hours, and CAST at the end to convert to format TIME thus:

select CAST(DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', hora)), '00:00:00.000') as time) 

Example:

declare @t table (hora time)
insert into @t values ('00:02:15')
insert into @t values ('00:09:47')

select CAST(DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', hora)), '00:00:00.000') as time) from @t

Note that the secret is to add the difference between the time '00:00:00' and the bank field time.

Based on this English OS response: https://stackoverflow.com/questions/19535718/sum-total-time-in-sql-server

Answer 3

The simplest way to do what you want is:

SELECT 
    ID, CONVERT(TIME,DATEADD (ms, SUM(DATEDIFF(MILLISECOND, 0, T)), 0)) AS TIME 
FROM 
    T1
GROUP BY 
    ID