Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Calculating Duration Using DATETIME Start and End Dates (SQL Spackle) RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

  • January 16, 2014 at 9:21 am

    #1680721

    Thomas Abraham (1/16/2014)

    Eirikur Eiriksson (1/16/2014)

    Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always greater than the start time. This is the reason why I prefer to use double cast, first to varbinary and then to bigint.

    SELECT

    StartDT

    ,EndDT

    ,Duration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

    ,DurationI = STUFF(CONVERT(VARCHAR(20),StartDT-EndDT,114),1,2,DATEDIFF(hh,0,StartDT-EndDT))

    ,cast(cast(StartDT as varbinary(128)) as bigint) As StartTicks

    ,cast(cast(EndDT as varbinary(128)) as bigint) AS EndTicks

    ,cast(cast(StartDT as varbinary(128)) as bigint)-cast(cast(EndDT as varbinary(128)) as bigint) as XmY

    ,cast(cast(EndDT as varbinary(128)) as bigint)-cast(cast(StartDT as varbinary(128)) as bigint) as YmX

    FROM #JBMTest;

    I got:

    Msg 8115, Level 16, State 2, Line 15

    Arithmetic overflow error converting expression to data type datetime.

    when I ran it for date differences greater than about 148 years. Maybe not a problem for some implementations, but something to be aware of.

    The error would be from either the Duration or DurationI as 148 years are well within the approx. 974904000 year limit of the bigint, given there are 300 ticks in a second. The binary-bigint method works on DATETIME and therefore inherits its limits:

    declare @t1 datetime = '9999-12-31 23:59:59.000'

    declare @t2 datetime = '1753-01-01 00:00:00.000'

    SELECT cast(cast(@t1 as varbinary(128)) as bigint)-cast(cast(@t2 as varbinary(128)) as bigint)