Post reply

Converting a Date to an Integer (Computed Column)

  • May 19, 2022 at 12:41 pm

    #4035569

    I have a computed column that subtracts a completion date from a start date and I'm trying to track the total time it takes to complete a job. However, the computed column returns the answer as a date. I'd like to return it as an integer instead. how do I do that?

    Thanks in advance!

  • May 19, 2022 at 2:29 pm

    #4035636

    Well what unit of time do you want to see?

  • May 19, 2022 at 2:41 pm

    #4035640

    You would use DATEDIFF with the units difference you would like to see.  I would recommend using a unit one step below the actual requirement unless you only want to show whole values.

    For example - if you want the number of days difference: DATEDIFF(hour, start_date, completion_date) / 24.0.  Given a start date and time of '2022-05-18 08:15:36.333' and a completion date and time of '2022-05-19 09:39:38.557' the result would be 1.041666.  If you just showed the difference in days - it would have a value of 1.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • May 19, 2022 at 3:26 pm

    #4035710

    pekoms wrote:

    I have a computed column that subtracts a completion date from a start date and I'm trying to track the total time it takes to complete a job. However, the computed column returns the answer as a date. I'd like to return it as an integer instead. how do I do that?

    Thanks in advance!

    Do you want the difference in hours, minutes, and seconds or ????  The answer is pretty simple but we need to know so we can post the correct code.  Also, remember that DATEDIFF does NOT calculate duration... DATEDIFF only counts boundaries crossed.  Two date times with only a 3.3 milli-second difference can come back with 1 for a DATEPART of YEAR.

     

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

  • June 13, 2022 at 12:34 pm

    This was removed by the editor as SPAM

  • June 13, 2022 at 2:40 pm

    #4048772

    The spam entry brought me back here.

    @pekoms... you never got back to me on what unit of measure you wanted the result to actually be.

    Just in case you still have your ears on this thread, please see the following article on the subject.

    https://www.sqlservercentral.com/articles/calculating-duration-using-datetime-start-and-end-dates-sql-spackle-2

    I'll also tell you that while the difference of two datetimes appears to be stored as a date, it's actually stored as two integers behind the scenes but you don't have to worry about that.  Just convert it to a FLOAT... the answer returned will appear as a decimal number that represents the number of whole 24 hour periods that have passed and the decimal portion represents the part of a 24 hour period as the "partial day" that has passed.

    The article explains all of that and one way to display it.  There are many other possibilities to display (and store) the duration as but we need to know what you want that to be.  The cool part is, the converted number can be used with SUM and anything else that can handle plain ol' numbers.

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

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

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