Post reply

Convert Sting value to DateTime2

  • May 19, 2020 at 8:29 pm

    #3753048

    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

  • May 19, 2020 at 8:42 pm

    #3753054 

    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

  • May 19, 2020 at 8:45 pm

    #3753056 

     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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 19, 2020 at 8:52 pm

    #3753059

    Thank you !! Working as expected

  • May 19, 2020 at 9:28 pm

    #3753067

    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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 19, 2020 at 9:48 pm

    #3753080

    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

  • May 19, 2020 at 10:41 pm

    #3753093

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

  • May 20, 2020 at 12:09 am

    #3753116

    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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 20, 2020 at 1:50 am

    #3753124

    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

  • May 20, 2020 at 4:44 am

    #3753139

    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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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