How can I get the sum of several date and time values?

Question

I have a request, so the result of the request:

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 a sum of these values; how can i get it?

Answer 1

Convert minutes to seconds

SUM() seconds

Convert back to minutes

---

The following will give you SUM seconds:

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

Then it turns into a datetime object:

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

---

Or as 1 request:

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

Answer 2

You cannot summarize time. Instead, you should use the DateDiff function with your start and end time using the seconds parameter. This will return an integer data type on which SUM can be included. When you are done, convert it to a DateTime to format it as hours: minutes: seconds.