Home Forums SQL Server 2008 T-SQL (SS2K8) datediff producing erroneous result when datetime is between midnight and 1AM RE: datediff producing erroneous result when datetime is between midnight and 1AM

  • You'll need to format the hours separately, as below, since style "114" will only handle up to 23 hours:

    SELECT

    CAST(DATEDIFF(ms, ActualStartTime, EndTime) / 3600000 AS varchar(3)) +

    RIGHT(CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms, ActualStartTime, EndTime), 0), 114), 10) AS ELAPSED

    FROM (

    select cast('2013-09-13 17:49:36.000' as datetime) AS ActualStartTime,

    cast('2013-09-14 12:40:47.000' as datetime) AS EndTime

    union all

    select '2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000'

    ) AS test_data

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.