Home Forums SQL Server 2012 SQL Server 2012 - T-SQL DATEDIFF with days and hours RE: DATEDIFF with days and hours

  • February 18, 2013 at 5:16 pm

    #1588992

    Gabriel P (2/18/2013)

    Ian C0ckcroft (2/18/2013)

    Hi guys, I am trying to calculate the diffs between to days as a decimal. The hours will an easier wayybe in decimal.

    i can do it in varchar, but i need to be able to agrigate my value.

    eg.

    datediff(DD, [CreatedOn],getdate()) + CAST(DATEPART(HH, GETDATE())- DATEPART(HH, [CreatedOn]) AS REAL)/10

    this gives me the results i need.

    is there an easier way?

    Why are you dividing the difference in hours by 10? I'm guessing that's supposed to be 24 with a conversation to a float or decimal type.

    Either way I think this is what you are looking for

    Declare @create_date datetime = '1/1/2013', @execution_date DATETIME = GETDATE();

    SELECT CAST(@execution_date - @create_date AS REAL)

    Nice to see a kindred spirit. I normally use FLOAT for the same thing but that's basically the way I do it. Nice and simple.

    I wish they had made such simple calculations possible with the new date and time datatypes instead of trying to follow some bloody ANSI/ISO standard for the sake of the myth known as "portability".

    --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)