How can I get the sum of multiple datetime values?
41,858
Solution 1
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)
Solution 2
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.
Author by
Mehmet
Updated on July 05, 2022Comments
-
Mehmet about 2 years
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?