Home Forums SQL Server 2008 T-SQL (SS2K8) Is there an accurate script for datediff format in years:months:days? RE: Is there an accurate script for datediff format in years:months:days?

  • January 19, 2012 at 7:52 am

    #1435869

    Lowell (1/17/2012)

    i have tehse saved in my snippets; does this do what you want?

    --elapsed time/datediff

    select [Years] = datediff(year,0,ET-ST)-1,

    [Months] = datepart(month,ET-ST)-1,

    [Days] = datepart(day,ET-ST)-1,

    [Hours] = datepart(Hour,ET-ST),

    [Minutes] = datepart(Minute,ET-ST),

    [Seconds] = datepart(Second,ET-ST),

    [Milliseconds] = datepart(millisecond,ET-ST)

    from

    (

    select -- Test Data

    ST = convert(datetime,'2008/09/22 00:35:33.997'),

    ET = convert(datetime,'2009/10/23 04:05:45.443')

    ) a

    This doesn't work as expected, because it doesn't appropriately account for leap years. For example, the range 2011-01-01 to 2012-12-31 contains a leap year (2012). Using ET-ST essentially converts the range to 1900-01-01 to 1902-01-01 which does NOT contain a leap year (1900 though divisible by 4 is not a leap year, because it is not divisible by 400) and subsequently gives the wrong values for year, month, and day.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA