Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Calculating elapsed time from DATETIME

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

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe


Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data

Comments

Leave a comment on the original post [sqlandme.com, opens in a new window]

Loading comments...