Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data > SQL Server – Calculating elapsed time from DATETIME

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

About these ads
  1. Carlos Rodriguez
    December 31, 2013 at 4:36 pm

    First of all, excuse my English.
    There is an error in the calculation. I guess the product of some rounding.
    The result should be “4 days 13 hours 1 minute and 55 seconds.”
    Try it with the ending date SET @ endTime = ‘2013-11-10 1:22:40 ‘(add 10 seconds) and see that the result is lower than before.

  2. Charles Brochu
    December 31, 2013 at 8:18 pm

    Indeed :

    DECLARE @startTime DATETIME = ‘2013-11-05 12:20:35′
    DECLARE @endTime DATETIME = ‘2013-11-10 01:22:30′

    SELECT CONVERT( VARCHAR(3), DATEDIFF(HH,@startTime,@endTime)/24 ) + ‘:’ +
    CONVERT( VARCHAR(12), DATEADD(S,DATEDIFF(S,@startTime,@endTime),0), 114 )

    displays 4:13:01:55:000

  3. cmartin16
    December 31, 2013 at 8:54 pm

    Carlos has a point. Because of the way we divide up time, finding differences causes the type of errors he identified. This is some old code I wrote up a long time ago. I’m sure there’s a more elegant way of doing it (there must be), but this returns the correct values:

    declare @days int, @hours int, @minutes int, @seconds int
    DECLARE @startTime DATETIME = ‘2013-11-05 12:20:35′
    DECLARE @endTime DATETIME = ‘2013-11-10 01:22:30′
    select @seconds = datediff(second, @startTime, @endTime)

    select @days = (@seconds / (24 * 3600)), @seconds = (@seconds % (24 * 3600))
    select @hours = @seconds / 3600, @seconds = @seconds % 3600
    select @minutes = @seconds / 60, @seconds = @seconds % 60
    select @days, @hours, @minutes, @seconds

  4. Nate Schmidt
    January 1, 2014 at 12:07 am

    I think the fix to the error is this:
    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)

  5. January 2, 2014 at 9:26 pm

    Nice code, thank you.
    I frequently use 2-digit formatting for this, so here is my suggestion although I typically use an inline or scalar function for this.
    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] =
    RIGHT(’00’ + CAST((DATEDIFF(HOUR, @startTime, @endTime) / 24) AS VARCHAR), 2)
    + ‘:’ +
    RIGHT(’00’ + CAST((DATEDIFF(HOUR, @startTime, @endTime) % 24) AS VARCHAR), 2)
    + ‘:’ +
    CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime)
    THEN RIGHT(’00’ + CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR), 2)
    ELSE
    RIGHT(’00’ + CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60) AS VARCHAR), 2)
    END
    + ‘:’ + RIGHT(’00’ + CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR), 2) ,
    [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 ‘

  6. January 3, 2014 at 3:28 am

    Perhaps a more readable way of accomplishing this would be as follows. It’s also a good way to code inside a function or stored procedure with start and end times passed as parameters. I also included formulas I use in functions and stored procedures for date rounding, which can be very tricky and easily miscalculated.

    DECLARE @StartTime datetime, @EndTime datetime;
    SET @StartTime = ‘2013-11-05 12:20:35′;
    SET @EndTime = ‘2013-11-10 01:22:30′;

    DECLARE @Diff INT;
    SET @Diff = DATEDIFF(SECOND, @StartTime, @EndTime);

    — DD:HH:MM:SS format
    SELECT
    CONVERT(varchar(10), (@Diff/86400)) + ‘:’ +
    CONVERT(varchar(10), ((@Diff%86400)/3600)) + ‘:’ +
    CONVERT(varchar(10), (((@Diff%86400)%3600)/60)) + ‘:’ +
    CONVERT(varchar(10), (((@Diff%86400)%3600)%60))
    AS ‘DD:HH:MM:SS';

    — String format
    SELECT
    CONVERT(varchar(10), (@Diff/86400)) + ‘d ‘ +
    CONVERT(varchar(10), ((@Diff%86400)/3600)) + ‘h ‘ +
    CONVERT(varchar(10), (((@Diff%86400)%3600)/60)) + ‘m ‘ +
    CONVERT(varchar(10), (((@Diff%86400)%3600)%60)) + ‘s ‘
    AS ‘StringFormat';

    — ####### Date Rounding ##############################################################################################

    — Rounded to nearest minute
    SELECT
    CONVERT(varchar(10), (@Diff/86400) +
    CASE
    WHEN ((@Diff%86400 + CASE WHEN (((@Diff%86400)%3600)%60) >= 30 THEN 60 ELSE 0 END)/3600) = 24
    THEN 1
    ELSE 0
    END) + ‘d ‘ +
    CONVERT(varchar(10),
    CASE
    WHEN ((@Diff%86400 + CASE WHEN (((@Diff%86400)%3600)%60) >= 30 THEN 60 ELSE 0 END)/3600) = 24
    THEN 0
    ELSE ((@Diff%86400 + CASE WHEN (((@Diff%86400)%3600)%60) >= 30 THEN 60 ELSE 0 END)/3600)
    END) + ‘h ‘ +
    CONVERT(varchar(10),
    CASE
    WHEN ((((@Diff%86400)%3600)/60) + CASE WHEN (((@Diff%86400)%3600)%60) >= 30 THEN 1 ELSE 0 END) = 60
    THEN 0
    ELSE ((((@Diff%86400)%3600)/60) + CASE WHEN (((@Diff%86400)%3600)%60) >= 30 THEN 1 ELSE 0 END)
    END) + ‘m ‘
    AS ‘RoundedToNearestMinute';

    — Rounded to nearest hour
    SELECT
    CONVERT(varchar(10), (@Diff/86400) +
    CASE
    WHEN ((@Diff%86400)/3600) + CASE WHEN (((@Diff%86400)%3600)/60) >= 30 THEN 1 ELSE 0 END = 24
    THEN 1
    ELSE 0
    END) + ‘d ‘ +
    CONVERT(varchar(10),
    CASE
    WHEN ((@Diff%86400)/3600) + CASE WHEN (((@Diff%86400)%3600)/60) >= 30 THEN 1 ELSE 0 END = 24
    THEN 0
    ELSE ((@Diff%86400)/3600) + CASE WHEN (((@Diff%86400)%3600)/60) >= 30 THEN 1 ELSE 0 END
    END) + ‘h ‘
    AS ‘RoundedToNearestHour';

    — Rounded to nearest day
    SELECT
    CONVERT(varchar(10), (@Diff/86400) + CASE WHEN ((@Diff%86400)/3600) >= 12 THEN 1 ELSE 0 END) + ‘d ‘
    AS ‘RoundedToNearestDay';

  7. Peter E. Kierstead
    January 7, 2014 at 3:54 am

    IMHO Charles Brochu is on the right track {and this has nothing to do with the fact that I do this close to the same way… really!}. My twist is that I need my timings down to the millisecond (ya, SQL fudges on this; older versions anyway) so I use the following code (this same technique could be used to obtain elapsed time at micro/nano seconds also):

    Use PeterK;
    If Object_Id(‘dbo.ElapsedTime’,’fn’) is not Null Drop Function dbo.ElapsedTime;
    Go
    Create Function dbo.ElapsedTime
    (
    @st DateTime,
    @et DateTime
    )
    Returns VarChar(18)
    As
    Begin
    — Reality check!
    If (@st>@et) Return Null;

    — Return appropriately formatted elapsed time components.
    Return
    — First, output the days part as it could cause DateDiff(ms,…) to overflow.
    Cast(DateDiff(dd,@st,@et) as VarChar)
    +':’
    — Do DateDiff(ms,…) on time parts only (days already handled above).
    +Right
    (
    Convert
    (
    VarChar,
    DateAdd
    (
    ms,
    DateDiff
    (
    ms,
    Right(Convert(VarChar,@st,121),12),
    Right(Convert(VarChar,@et,121),12)
    ),
    0
    ),
    121
    ),
    12
    );
    End
    Go
    Select
    dbo.ElapsedTime(‘2013-01-01 10:00:00.123 AM’,’2013-01-02 12:03:04.678 PM’),
    dbo.ElapsedTime(‘2013-01-03 10:00:00.123 AM’,’2013-01-03 10:00:00.123 AM’),

    dbo.ElapsedTime(‘2050-01-01 10:00:00.123 AM’,’2013-01-03 11:02:03.678 AM’),

    dbo.ElapsedTime(Null,’2013-01-03 10:00:00.000 AM’),
    dbo.ElapsedTime(‘2013-01-03 10:00:00.000 AM’,Null),
    dbo.ElapsedTime(Null,Null);

  8. Paul Siracusa
    March 26, 2014 at 8:40 pm

    This should simplify and reduce errors.

    select cast((datediff(second, min(@startTime), max(@endTime)) /60/60/24) as varchar)
    + ‘:’ +
    cast(datepart(hour,(max(@EndTime)-min(@StartTime))) as varchar)
    + ‘:’ +
    cast(datepart(minute,(max(@EndTime)-min(@StartTime))) as varchar)
    + ‘:’ +
    cast(datepart(second,(max(@EndTime)-min(@StartTime))) as varchar)

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 268 other followers

%d bloggers like this: