Convert Sting value to DateTime2

  • Hello,

    I am trying to convert '2020-04-29-14.41.26.686978' this value to DateTime2. Can you please suggested the best way.

    Thanks,

    Sab

  • Declare @dateString varchar(30) = '2020-04-29-14.41.26.686978';

    Select *
    , DateOnly = left(d.dateString, 10)
    , TimeOnly = substring(d.dateString, 12, 8)
    , MillisecondsOnly = right(d.dateString, 7)
    , OutputDatetime2 = cast(concat(left(d.dateString, 10), ' ', replace(substring(d.dateString, 12, 8), '.', ':'), right(d.dateString, 7)) As datetime2(7))
    From (Values (@dateString)) As d(dateString);

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  •  SELECT CONVERT(DATETIME2(7), STUFF(REPLACE(STUFF('2020-04-29-14.41.26.686978',11,1,'T'),'.',':'),20,1,'.'));

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Thank you !! Working as expected

  • sabarishbabu wrote:

    Thank you !! Working as expected

    You're welcome but which one?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Shoot - I usually see the STUFF option but missed it on this one...and putting in the 'T' was on my mind but it is getting late 😉

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Thank you Jeff Moden - STUFF one :). however thank you both 🙂

  • Thank you for the feedback, Sabarish (I hope I split your name correctly).  Just to be sure, though, do you understand what STUFF does?  I ask only because a whole lot of people have never heard of it before.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Thank again Jeff!!

    STUFF() deletes a part of a string and then inserts another part into the string, starting at a specified position.

    Step 1 : Add T between Date and Time using STUFF()

    SELECT STUFF('2020-04-29-14.41.26.686978', 11, 1, 'T') -- 2020-04-29T14.41.26.686978

    Step 2 : Replace all the [.] with [:]

    SELECT REPLACE(STUFF('2020-04-29-14.41.26.686978', 11, 1, 'T'), '.', ':') -- 2020-04-29T14:41:26:686978

    Step 3 : Replace  20th position [:] with [.] using STUFF()

    SELECT STUFF(REPLACE(STUFF('2020-04-29-14.41.26.686978', 11, 1, 'T'), '.', ':'), 20, 1, '.'); -- 2020-04-29T14:41:26.686978

    Please correct me if I am wrong on this. Appreciate your help

  • Nope... you've got it.  The 2nd operand tells what character position to start working with.  The 3rd operand identified how many characters to replace.  It can be "0" which means don't replace anything... just to the "stuff" into that position.  In both cases, I replace one character with another.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 10 posts - 1 through 10 (of 10 total)

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