# 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.

• ` 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!"

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!"

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.

• 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!"

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!"

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