Convert DateDiff into Hours, Minutes, Seconds, Milliseconds

  • SQL Espo

    SSCrazy

    Points: 2674

    I have the following T-SQL code to change the difference between datetime columns into hours, minutes, and seconds

    CONVERT(varchar(6), DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp)/3600) + ':' + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp) % 3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp) % 60), 2)

    but I also wanted milliseconds. I have searched and have not found anything. Does anyone have any ideas?

    Thanks!

    Michael

  • andrewd.smith

    SSCrazy Eights

    Points: 8444

    This will return the time difference including milliseconds. It will work fine if the time difference is guaranteed to be less than 24 hours.

    However, if the time difference is greater than 24 hours, it will not give you what you want as it will display the number of hours modulo 24. Also, the time difference calculation will overflow if the difference is greater than about 24.8 days (when number of milliseconds >= 2e31).

    DECLARE @dt1 datetime

    DECLARE @dt2 datetime

    SELECT @dt1 = '20090316 12:00:00.000', @dt2 = GETDATE()

    SELECT CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @dt1, @dt2), 0), 114)

  • SQL Espo

    SSCrazy

    Points: 2674

    Perfect! Thanks!!

  • UKGav

    Say Hey Kid

    Points: 678

    I found a script on the net and altered it for my needs.

    I'd like to know what others think of this and if you can envisage any problems with it?

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROC [dbo].[zTest_SP_DATEDIFF_TIMER] AS

    -- CREATED: 18/08/2009 by Gav B.

    -- DESCRIPTION: This procedure calculates the difference between 2 dates right down to the millisecond (0.003sec accuracy in SQL2005, untested in 2008)

    --

    -- NOTES:

    -- @I_OverFlows = DATEDIFF(ms,GETDATE()-24.49,GETDATE())

    -- This represents the maximum of approx 24 Days 11 Hours 50 Mins before causing an "overflow at runtime".

    -- RAND function used below just to give a random number for demonstration purposes.

    -- Unknown at time of writting WHY the combination of % and / works, but it seems to quite well...

    DECLARE @I INT

    SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())

    SELECT

    convert(varchar(10), (@I/86400000)) + ' Days ' +

    convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+

    convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+

    convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +

    convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]

    - Gav B.

  • andrewd.smith

    SSCrazy Eights

    Points: 8444

    If you must produce formatted output directly from SQL rather than in the front end then your approach is reasonable, however, there are some unecessary modulo operations in your expressions, e.g. ((@I%86400000)%3600000)%60000 is equivalent to @I%60000, so your statement can be rewritten as:

    SELECT

    convert(varchar(10), @I/86400000) + ' Days ' +

    convert(varchar(10), (@I%86400000)/3600000) + ' Hours '+

    convert(varchar(10), (@I%3600000)/60000) + ' Mins '+

    convert(varchar(10), (@I%60000)/1000) + ' sec ' +

    convert(varchar(10), @I%1000) + ' ms ' AS [DD:HH:MM:SS:MS]

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    This is a little simpler and will work with any date range.

    select

    *,

    Days = datediff(dd,0,DateDif),

    Hours = datepart(hour,DateDif),

    Minutes = datepart(minute,DateDif),

    Seconds = datepart(second,DateDif),

    MS = datepart(ms,DateDif)

    from

    (

    select

    DateDif = EndDate-StartDate,

    aa.*

    from

    ( -- Test Data

    Select

    StartDate = convert(datetime,'20090213 02:44:37.923'),

    EndDate = convert(datetime,'20090715 13:24:45.837')

    ) aa

    ) aResults:

    DateDif StartDate EndDate Days Hours Minutes Seconds MS

    ----------------------- ----------------------- ----------------------- ---- ----- ------- ------- ---

    1900-06-02 10:40:07.913 2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 152 10 40 7 913

    (1 row(s) affected)

  • jcrawf02

    SSC-Insane

    Points: 24198

    Gav B (8/18/2009)


    I found a script on the net and altered it for my needs.

    I'd like to know what others think of this and if you can envisage any problems with it?

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROC [dbo].[zTest_SP_DATEDIFF_TIMER] AS

    -- CREATED: 18/08/2009 by Gav B.

    -- DESCRIPTION: This procedure calculates the difference between 2 dates right down to the millisecond (0.003sec accuracy in SQL2005, untested in 2008)

    --

    -- NOTES:

    -- @I_OverFlows = DATEDIFF(ms,GETDATE()-24.49,GETDATE())

    -- This represents the maximum of approx 24 Days 11 Hours 50 Mins before causing an "overflow at runtime".

    -- RAND function used below just to give a random number for demonstration purposes.

    -- Unknown at time of writting WHY the combination of % and / works, but it seems to quite well...

    DECLARE @I INT

    SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())

    SELECT

    convert(varchar(10), (@I/86400000)) + ' Days ' +

    convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+

    convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+

    convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +

    convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]

    - Gav B.

    I can envisage a problem. You didn't credit your source for the script that you "found on the net". Granted, you altered it, but alteration is not authoring, especially since your alteration may have been a minor detail, we have no way of knowing.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    jcrawf02 (8/19/2009)


    Gav B (8/18/2009)


    I found a script on the net and altered it for my needs.

    I'd like to know what others think of this and if you can envisage any problems with it?

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROC [dbo].[zTest_SP_DATEDIFF_TIMER] AS

    -- CREATED: 18/08/2009 by Gav B.

    -- DESCRIPTION: This procedure calculates the difference between 2 dates right down to the millisecond (0.003sec accuracy in SQL2005, untested in 2008)

    --

    -- NOTES:

    -- @I_OverFlows = DATEDIFF(ms,GETDATE()-24.49,GETDATE())

    -- This represents the maximum of approx 24 Days 11 Hours 50 Mins before causing an "overflow at runtime".

    -- RAND function used below just to give a random number for demonstration purposes.

    -- Unknown at time of writting WHY the combination of % and / works, but it seems to quite well...

    DECLARE @I INT

    SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())

    SELECT

    convert(varchar(10), (@I/86400000)) + ' Days ' +

    convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+

    convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+

    convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +

    convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]

    - Gav B.

    I can envisage a problem. You didn't credit your source for the script that you "found on the net". Granted, you altered it, but alteration is not authoring, especially since your alteration may have been a minor detail, we have no way of knowing.

    The solution has a fairly serious limitation in only working over a fairly short of time, less than 25 days, so maybe it's better he didn't give credit.

  • UKGav

    Say Hey Kid

    Points: 678

    The solution has a fairly serious limitation in only working over a fairly short of time, less than 25 days, so maybe it's better he didn't give credit.

    Thank you for script and yes thats quite right, my altertered version of the original was quite limited.

    The original was only to the second not millisecond and as such worked just fine.

    I was just pointing out that I haven't written the whole thing from scratch.

    Next I need to wrap it all up into a SP or Function... hmm...

  • macintyre_bernie

    SSC Enthusiast

    Points: 122

    [font="Courier New"][/font]DECLARE @BatchStart datetime = GETDATE(), @StageStart datetime, @Iteration int = 0, @Iterations int = 5

    PRINT 'Batch start: ' + CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @BatchStart, @BatchStart), 0), 114)

    WHILE @Iteration <> @Iterations BEGIN

    SELECT @Iteration = @Iteration + 1, @StageStart = GETDATE()

    WAITFOR DELAY '00:00:01'

    PRINT ' Iteration ' + CAST(@Iteration as varchar(3)) + ': ' + CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @StageStart, GETDATE()), 0), 114)

    END

    PRINT 'Batch time : ' + CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @BatchStart, GETDATE()), 0), 114)

    Good for 24h, but can easily be adjusted for more.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply