How to convert INTEGER to TIME

  • Jeff Moden

    SSC Guru

    Points: 994661

    My bad... looking back on this, I see where you took his suggestion early on. Sorry and thanks for the feedback.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • dalviprasad31

    Grasshopper

    Points: 17

    Use below expression
    convert(char(8), dateadd(second, [int_datatype_column], ''), 114)

  • gvoshol 73146

    Hall of Fame

    Points: 3101

    dalviprasad31 - Monday, July 23, 2018 6:33 AM

    Use below expression
    convert(char(8), dateadd(second, [int_datatype_column], ''), 114)

    I know you're a new user.

    But you responded to a 9 year old thread in the SQL Server 2005 forum, with a solution that wasn't introduced until SQL Server 2008.

    And your solution only works if the number of seconds is less than that in one day.  The OP stated this would be OK in his application, but we don't want to post generic solutions that others might read without realizing the limitation.

  • Sue_H

    SSC Guru

    Points: 90260

    gvoshol 73146 - Tuesday, July 24, 2018 6:04 AM

    dalviprasad31 - Monday, July 23, 2018 6:33 AM

    Use below expression
    convert(char(8), dateadd(second, [int_datatype_column], ''), 114)

    I know you're a new user.

    But you responded to a 9 year old thread in the SQL Server 2005 forum, with a solution that wasn't introduced until SQL Server 2008.

    And your solution only works if the number of seconds is less than that in one day.  The OP stated this would be OK in his application, but we don't want to post generic solutions that others might read without realizing the limitation.

    Microsoft's documentation has "SQL Server (starting with 2008)" even if it's something that's been available since many many versions ago (convert, dateadd). It's incredibly misleading for just about everyone.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 994661

    gvoshol 73146 - Tuesday, July 24, 2018 6:04 AM

    dalviprasad31 - Monday, July 23, 2018 6:33 AM

    Use below expression
    convert(char(8), dateadd(second, [int_datatype_column], ''), 114)

    I know you're a new user.

    But you responded to a 9 year old thread in the SQL Server 2005 forum, with a solution that wasn't introduced until SQL Server 2008.

    And your solution only works if the number of seconds is less than that in one day.  The OP stated this would be OK in his application, but we don't want to post generic solutions that others might read without realizing the limitation.

    There's nothing in that code that hasn't been around since (IIRC) at least SQL Server 6.5.  Like Sue said, MS has publishes dates for these things based on what they support and 2008 just went out of support. 

    I will admit, though, that it doesn't solve the OP's original problem of returning the answer in the MINUTES:SECONDS format.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

Viewing 5 posts - 16 through 20 (of 20 total)

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