Home Forums SQL Server 2012 SQL 2012 - General Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS RE: Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS

  • June 15, 2014 at 9:55 am

    #1721794

    Eirikur Eiriksson (6/15/2014)

    Didn't see Lynn's code before I wrote the example, almost the same but without a string thingy

    😎

    /*Converting INT date and time to datetime */

    DECLARE @INT_YYYYMMDD INT = 20140704;

    DECLARE @INT_HHMMSS INT = 012056;

    SELECT DATEADD(SECOND,

    (((@INT_HHMMSS / 10000) * 3600) -- hours to seconds

    + (((@INT_HHMMSS / 100) % 100) * 60) -- minutes to seconds

    + (@INT_HHMMSS % 100)) -- seconds

    , CONVERT(DATETIME2(0),CAST(@INT_YYYYMMDD AS VARCHAR(8)),112))

    Results

    2014-07-04 01:20:56

    Just curious... since the use of CONVERT kills the possibility of portability anyway (and I don't believe in portable code for anything but C.R.U.D.), why did you feel it necessary to use DATETIME2? Definitely not a challenge here... just curious.

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