How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.

Here's the example I want to sum:

00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50

Hi!

declare @table table
(
value time
)

insert into @table
values 
('02:01:30'),
('01:28:10'),
('01:01:50'),
('06:50:30'),
('00:01:50')

;
WITH CTE as
(
select SUM((DATEPART(HOUR, value) * 60 * 60) + (DATEPART(MINUTE, value) * 60) + DATEPART(SECOND, value)) as seconds from @table
)
SELECT 
CONVERT(varchar, DATEADD(ms, ( CTE.seconds % 86400 ) * 1000, 0), 114)
as 'HH:MM:SS.MS'
FROM CTE

• Proposed as answer by Ricardo Lacerda 13 hours 21 minutes ago

Perfect - awesome !  Thank you!!

If they are indeed times, you could do it like this:

DECLARE @times TABLE (value TIME)
INSERT INTO @times (value) VALUES
('02:01:30'),('01:28:10'),('01:01:50'),('06:50:30'),('00:01:50')

SELECT 
RIGHT('000' + CAST(SUM((DATEDIFF(SECOND,'1900-01-01',value) / 86400))        AS VARCHAR),3) + ':' +
RIGHT( '00' + CAST(SUM((DATEDIFF(SECOND,'1900-01-01',value) % 86400)) / 3600 AS VARCHAR),2) + ':' +
RIGHT( '00' + CAST(SUM((DATEDIFF(SECOND,'1900-01-01',value) % 3600) ) / 60   AS VARCHAR),2) + ':' +
RIGHT( '00' + CAST(SUM((DATEDIFF(SECOND,'1900-01-01',value) % 60)   )        AS VARCHAR),2) AS timeString
  FROM @times

I'll create a follow up post for what to do if your values match the values you provided (which I'm assuming are DD:HH:MM:SS)

So, if your example data was accurate (in which case I'm assuming VARCHAR data types), try this:

DECLARE @times TABLE (value VARCHAR(11))
INSERT INTO @times (value) VALUES
('01:02:01:30'),('00:23:28:10'),('00:01:01:50'),('00:06:50:30'),('00:00:01:50')

SELECT --
RIGHT('000' + CAST(SUM(CAST(LEFT(value,2) AS INT)) + CAST(ROUND(SUM((DATEDIFF(SECOND,'1900-01-01',CAST(RIGHT(value,8) AS TIME)) / 86400.0)),0) AS INT) AS VARCHAR),3) + ':' +
RIGHT( '00' + CAST(SUM((DATEDIFF(SECOND,'1900-01-01',CAST(RIGHT(value,8) AS TIME)) % 86400)) / 3600 AS VARCHAR),2) + ':' +
RIGHT( '00' + CAST(SUM((DATEDIFF(SECOND,'1900-01-01',CAST(RIGHT(value,8) AS TIME)) % 3600) ) / 60   AS VARCHAR),2) + ':' +
RIGHT( '00' + CAST(SUM((DATEDIFF(SECOND,'1900-01-01',CAST(RIGHT(value,8) AS TIME)) % 60)   )        AS VARCHAR),2) AS timeString
  FROM @times

Hi P D Brent,

If the data in your post is in a pattern 'dd:hh:mm:ss', to get an aggregated sum in that pattern as well, you can reference the below sample.

DECLARE @T TABLE(timeString VARCHAR(99))
INSERT INTO @T 
VALUES 
('00:02:01:30'),
('00:01:28:10'),
('00:01:01:50'),
('00:06:50:30'),
('00:00:01:50');

;WITH Cte AS
(
SELECT CAST(LEFT(timeString,2) AS INT)*86400+DATEDIFF(SECOND,0,CAST(RIGHT(timeString,8) AS TIME)) Seconds FROM @T
)
SELECT RIGHT('0'+LTRIM(STR(DATEDIFF(DAY,0,DATEADD(SECOND,SUM(Seconds),0)))),2)+':'+CAST(CAST(DATEADD(SECOND,SUM(Seconds),0) AS TIME) AS VARCHAR(8)) FROM Cte
 

If you have any feedback on our support, you can click here.

>> How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I  have read it's necessary to convert to seconds, sum then convert back, but I'm' wondering if there's an alternative. <<

The list of things your posted are not TIME. A time data type is displayed with "hh:mm:ss.sss.." and you have four sub-fields. Next, it makes no sense to do math on a TIME. What is Christmas divided by Ramadan?  You had to meanduration instead. the only unit of temporal duration is the second, which is why you read that correct advice. 

Because you failed to post DDL, everyone had to guess if you were using strings, time stamps or whatever in this posting. A lot of noobs will use TIME displaced from CAST ('00:00:00' AS TIME) and then use seconds.  Later, you will use {start_timestamp. end_timestamp} pairs for duration. 

Get a free download copy of the Rick Snodgrass book on Temporal data in SQL from the University of Arizona. It is a classic.