Dfyjkt

I need to calculate the total length in terms of Hours, Minutes, Seconds, and the average length, given some data with start time and end time.

For example the result must be something like 45:15:10 which means 45 hours 15 min 10 sec, or 30:07 for 30 min 07 sec.

We're using SQL Server 2008 R2 and the conversion failed when time is more than 24:59:59. Any idea of how I could do this?

For information, the columns in the table are Id, StartDateTime, EndDateTime, etc. I need to make a monthly report which contains the recordings count of the month, the total length of these records, and the average length. I'd like to know if there is an easy way to perform all of this.

You shouldn't be converting to time - it is meant to store a point in time on a single 24h clock, not a duration or interval (even one that is constrained on its own to < 24 hours, which clearly your data is not). Instead you can take the datediff in the smallest interval required (in your case, seconds), and then perform some math and string manipulation to present it in the output format you need (it might also be preferable to return the seconds to the application or report tool and have it do this work).

DECLARE @d TABLE
(
 id INT IDENTITY(1,1), 
 StartDateTime DATETIME, 
 EndDateTime DATETIME
);

INSERT @d(StartDateTime, EndDateTime) VALUES 
(DATEADD(DAY, -2, GETDATE()), DATEADD(MINUTE, 15, GETDATE())),
(GETDATE() , DATEADD(MINUTE, 22, GETDATE())),
(DATEADD(DAY, -1, GETDATE()), DATEADD(MINUTE, 5, GETDATE())),
(DATEADD(DAY, -4, GETDATE()), DATEADD(SECOND, 14, GETDATE()));

;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
 d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
 a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
 FROM @d
)
SELECT id, StartDateTime, EndDateTime,
 [delta_HH:MM:SS] = CONVERT(VARCHAR(5), d/60/60)
 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
 [avg_HH:MM:SS] = CONVERT(VARCHAR(5), a/60/60)
 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;

Results:

id StartDateTime EndDateTime delta_HH:MM:SS avg_HH:MM:SS
-- ------------------- ------------------- -------------- ------------
1 2013-01-19 14:24:46 2013-01-21 14:39:46 48:15:00 42:10:33
2 2013-01-21 14:24:46 2013-01-21 14:46:46 0:22:00 42:10:33
3 2013-01-20 14:24:46 2013-01-21 14:29:46 24:05:00 42:10:33
4 2013-01-17 14:24:46 2013-01-21 14:25:00 96:00:14 42:10:33

This isn't precisely what you asked for, as it won't show just MM:SS for deltas < 1 hour. You can adjust that with a simple CASE expression:

;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
 d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
 a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
 FROM @d
)
SELECT id, StartDateTime, EndDateTime,
 [delta_HH:MM:SS] = CASE WHEN d >= 3600 THEN 
 CONVERT(VARCHAR(5), d/60/60) + ':' ELSE '' END
 + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
 [avg_HH:MM:SS] = CASE WHEN a >= 3600 THEN 
 CONVERT(VARCHAR(5), a/60/60) + ':' ELSE '' END
 + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;

This query changes the delta column in the 2nd row in the above result from 0:22:00 to 22:00.

SELECT CONVERT(time, 
 DATEADD(mcs, 
 DATEDIFF(mcs, 
 '2007-05-07 09:53:00.0273335', 
 '2007-05-07 09:53:01.0376635'), 
 CAST('1900-01-01 00:00:00.0000000' as datetime2)
 )
 )

If you want to do averages, then the best approach is to convert to seconds or fractions of a day. Day fractions are convenient in SQL Server, because you can do things like:

select avg(cast(endtime - starttime) as float)
from t

You can convert it back to a datetime using the reverse cast:

select cast(avg(cast(endtime - starttime as float) as datetime)
from t

The arithmetic to get the times in the format you want . . . that is a pain. You might consider including days in the final format, and using:

select right(convert(varchar(255), <val>, 120), 10)

To get the hours exceeding 24, here is another approach:

select cast(floor(cast(<val> as float)*24) as varchar(255))+right(convert(varchar(255), <val>, 120), 6)

It uses convert for minutes and seconds, which should be padded with 0s on the left. It then appends the hours as a separate value.

I slightly modified Avinash's answer as it may end with error if difference is too big. If you need only HH:mm:ss it is sufficient to distinguish at seconds level ony like this:

SELECT CONVERT(time, 
 DATEADD(s, 
 DATEDIFF(s, 
 '2018-01-07 09:53:00', 
 '2018-01-07 11:53:01'), 
 CAST('1900-01-01 00:00:00.0000000' as datetime2)
 )
)

A way that avoids overflows and can include days and go all the way to milliseconds in the output:

DECLARE @startDate AS DATETIME = '2018-06-01 14:20:02.100'
DECLARE @endDate AS DATETIME = '2018-06-02 15:23:09.000'
SELECT CAST(DATEDIFF(day,'1900-01-01', @endDate - @startDate) AS VARCHAR) + 'd ' + CONVERT(varchar(22), @endDate - @startDate, 114)

The above will return

1d 01:03:06:900

And, off course, you can use the formatting of your choice

SQL Supports datetime substraction which outputs a new datetime relative to the MIN date (for instance 1900-01-01, you can probably get this value from some system variable) This works better than DATEDIFF, because DATEDIFF will count ONE for each "datepart boundaries crossed", even if the elapsed time is less than a whole datapart. Another nice thing about this method is that it allows you to use the date formatting conversions.