Home Forums SQL Server 2005 T-SQL (SS2K5) Convert DateDiff into Hours, Minutes, Seconds, Milliseconds RE: Convert DateDiff into Hours, Minutes, Seconds, Milliseconds

  • 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."