SQL Server – Calculating elapsed time from DATETIME. UPDATE 2020

Elapsed time can be calculated from DATETIME field by extracting number of hours/minutes and seconds. You can use below query to calculate elapsed time between two dates:

-- Vishal -

DECLARE @startTime DATETIME

DECLARE @endTime DATETIME

SET @startTime = '2013-11-05 12:20:35'

SET @endTime = '2013-11-10 01:22:30'

SELECT [DD:HH:MM:SS] =

CAST((DATEDIFF(HOUR, @startTime, @endTime) / 24) AS VARCHAR)

+ ':' +

CAST((DATEDIFF(HOUR, @startTime, @endTime) % 24) AS VARCHAR)

+ ':' +

CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime)

THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR)

ELSE

CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60)

AS VARCHAR)

END

+ ':' + CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR),

[StringFormat] =

CAST((DATEDIFF(HOUR , @startTime, @endTime) / 24) AS VARCHAR) +

' Days ' +

CAST((DATEDIFF(HOUR , @startTime, @endTime) % 24) AS VARCHAR) +

' Hours ' +

CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime)

THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR)

ELSE

CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60)

AS VARCHAR)

END +

' Minutes ' +

CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR) +

' Seconds '

Result Set:

DD:HH:MM:SS    StringFormat
4:13:2:55      4 Days 13 Hours 2 Minutes 55 Seconds

(1 row(s) affected)

[UPDATE 2020] Earlier query had an error in calculation, thanks to Carlos for pointing it out and Nate for providing the correct solution.