Archive

Posts Tagged ‘sql datediff function’

SQL Server – Calculating elapsed time from DATETIME

23.12.2013 8 comments

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 - http://SqlAndMe.com

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] Earlier query had an error in calculation, thanks to Carlos for pointing it out and Nate for providing the correct solution.
 
Hope This Helps!

Vishal