CODEWITHSUNDEEP
sql-servert-sqldatetimesum
Mehmet
Mehmet

Reputation: 1535

How can I get the sum of multiple datetime values?

I have a query such that the query's result is:

SELECT CONVERT(VARCHAR(8),(MAX(END_TIME)-MIN(START_TIME)),108) as DURATION WHERE ... GROUP BY TITLE

For each title, DURATION is different. The query returns:

DURATION
00:16:14
00:00:00
00:01:30
00:16:25
00:09:34
00:00:01
01:04:04
00:00:28
00:00:12
00:06:11
00:26:38
00:31:44
00:02:16
00:03:22
00:09:39
00:03:20
00:03:43
00:09:33
00:08:05
00:06:58
00:25:29
01:55:30
00:03:02
00:00:18
00:06:09
00:07:26
00:25:43
00:00:16
00:26:58
02:09:38
00:57:56
00:00:45
00:00:00
00:07:24
00:00:54
00:00:27
00:01:28
00:07:14
00:00:19
01:43:25
00:58:23
00:02:29
02:19:48
00:09:06
05:12:15
02:27:15
00:56:47
00:02:24

I need the sum of these values; how can I get it?

Upvotes: 12

Views: 52675

Answers (2)

Curtis
Curtis

Reputation: 103428

Convert minutes into seconds

SUM() the seconds

Convert back to minutes

The following will give you the SUM of seconds:

SET @Seconds = SELECT SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME]))

The following then turns that into a datetime object:

select convert(varchar(8), dateadd(second, @Seconds, 0),  108)

Or as 1 query:

SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME])), 0),  108)

Upvotes: 20

George Mastros
George Mastros

Reputation: 24506

You cannot sum times. What you should do instead is use the DateDiff function with your start and end time using the seconds parameter. This will return an integer data type which you can SUM on. When you're done, convert it to a DateTime to format it like hours:minutes:seconds.

Upvotes: 1

Related Questions