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 17, 2012 at 11:14 am

    #1434759

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

    --elapsed time/datediff

    select

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

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

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

    [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/22 04:05:45.443')

    ) a

    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

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!