Post reply

Displaying Time Differnece Between 2 DateTime Fields > 24 Hours

  • May 15, 2014 at 9:26 am

    #286764

    I have part of a query that subtracts 2 datetime fields and displays the difference in hh:mm:ss format. However, if the difference is > 24 hours, then it rolls over. How would I get the hours to display the total hours no matter what they are? If the difference is 28 hours, 42 minutes, it will display 04:42:00. I want it to display 28:42:00. Here is my query.

    CONVERT(VARCHAR(8), Case

    When WBITRAN_END_DATE Is NULL And WBITRAN_START_DATE Is Null Then

    GetDate() - WBITRAN_CREATE_DATE

    When WBITRAN_END_DATE Is NULL Then

    GetDate() - WBITRAN_START_DATE

    Else

    WBITRAN_END_DATE - WBITRAN_START_DATE

    End, 108) As Duration

  • May 15, 2014 at 9:52 am

    #1713914

    Use DateDiff (in seconds) then extract hours and minutes from the result (divide by 60, the remainder is seconds, divide the quotient by 60, the quotient is hours and the remainder is minutes)

  • May 15, 2014 at 10:08 am

    #1713919

    You might want to check this article as well. 😉

    http://www.sqlservercentral.com/articles/T-SQL/103343/

    It will explain how does this work.

    DECLARE @StartDT DATETIME

    ,@EndDT DATETIME

    ;

    SELECT @StartDT = '2000-01-01 10:30:50.780'

    ,@EndDT = '2000-01-02 12:34:56.789'

    ;

    --===== Display the dates and the desired format for duration

    SELECT StartDT = @StartDT

    ,EndDT = @EndDT

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 15, 2014 at 10:14 am

    #1713924

    Thanks for the replies. Good stuff.

  • May 15, 2014 at 12:12 pm

    #1713975

    Here is what I ended up doing which works perfectly for my needs.

    ,Right('0'+ Convert(VarChar, DateDiff(ss,Coalesce(WBITRAN_START_DATE, WBITRAN_CREATE_DATE),Coalesce(WBITRAN_END_DATE, GetDate())) / 3600), 2) + ':' +

    Right('0'+ Convert(VarChar, DateDiff(ss,Coalesce(WBITRAN_START_DATE, WBITRAN_CREATE_DATE),Coalesce(WBITRAN_END_DATE, GetDate())) % 3600 / 60), 2) + ':' +

    Right('0'+ Convert(VarChar, DateDiff(ss,Coalesce(WBITRAN_START_DATE, WBITRAN_CREATE_DATE),Coalesce(WBITRAN_END_DATE, GetDate())) % 60), 2) As Duration

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply