Convert Sting value to DateTime2

Question

Convert Sting value to DateTime2

sabarishbabu

Old Hand

Points: 371
More actions
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

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

 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)

sabarishbabu Old Hand Points: 371 More actions · Answer 3

sabarishbabu

Old Hand

Points: 371

May 19, 2020 at 8:52 pm

#3753059

Thank you !! Working as expected

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

sabarishbabu Old Hand Points: 371 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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)

sabarishbabu Old Hand Points: 371 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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)