How to Sum All Dates

I want to sum all the dates and show the total date sum. How can I do that in SQL 

For these dates

00:35

00.40

00.55

03.50

May 29th, 2015 2:52pm

Those aren't dates. They're not even times.

I'm assuming you're representing HH:mm, and that you meant 00:35, 00:40, 00:55 and 03:50?

DECLARE @tickTock TABLE (timeString CHAR(5))
INSERT INTO @tickTock (timeString) VALUES
('00:35'), ('00:40'), ('00:55'), ('03:50')

SELECT CONVERT(VARCHAR,DATEADD(MINUTE,SUM(CAST(CAST('1900-01-01 '+timeString+':00.000' AS DATETIME) AS DECIMAL(10,8)))*1440,'1900-01-01'),108)
  FROM @tickTock

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 3:05pm

Yes Patrick. Sorry it was HH:mm. It wasn't dates. Thanks for the great answer.
May 29th, 2015 3:07pm

In case your SUM breaches 24 hours:

DECLARE @tickTock TABLE (timeString CHAR(5))
INSERT INTO @tickTock (timeString) VALUES
('00:35'), ('00:40'), ('00:55'), ('03:50')

SELECT RIGHT('00'+CAST(DATEDIFF(DAY,'1900-01-01',CAST(DATEADD(MINUTE,SUM((LEFT(timeString,2)*60)+ RIGHT(timeString,2)),'1900-01-01') AS DATETIME)) AS VARCHAR),3)+':'+ CONVERT(VARCHAR,DATEADD(MINUTE,SUM((LEFT(timeString,2)*60)+ RIGHT(timeString,2)),'1900-01-01'),108)
  FROM @tickTock
Note: I've avoided using DATE and TIME datatypes because you didn't tell me what version you're using.
Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 3:24pm

This is great! Thanks
May 29th, 2015 3:25pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics