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

  • October 15, 2013 at 7:10 pm

    #1658769

    Nevyn (10/15/2013)

    The convert you are using is only showing the hours, minutes and seconds, and the answer you want is 1 day, 1 hour, 42 minutes, and 46 seconds

    You can try something like

    select convert(varchar,DATEDIFF(mi,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000')/60) + right(CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000'), 0), 114),10)

    to get hours over 24 in the hours count.

    You can also break it down yourself and avoid using date/string manipulation by using division and remainders, and converting the answers into strings

    Look again, please. There is absolutely no way that there are more than 25 hours between 10:05PM one day and 02:04AM the very next day.

    My apologies... I really misread that one! That's 00:22:05 and not 22:05 like I read it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)