CODEWITHSUNDEEP
sql-server-2008-r2
Premi
Premi

Reputation: 13

Convert String to Datetime or Datetime 2

I need help because I can't convert it by myself. I got the following String:

20160803093000000

Inside the String there is the Date and Time: 2016-08-03 09:30:00:00

I need it as Datetime or Datetime2.

Upvotes: 0

Views: 135

Answers (2)

Premi
Premi

Reputation: 13

SELECT CONVERT(DATETIME2,STUFF(STUFF(STUFF(STUFF(@t,9,0,' '),12,0,':'),15,0,':'),18,0,'.'))

Works fine! Thank you a lot.

Upvotes: 0

Setily
Setily

Reputation: 822

Here it is with the milliseconds (I entered another date, so you can see the change in the milliseconds):

DECLARE @t VARCHAR(50)
SET @t = '20160803093012345'

SELECT CONVERT(DATETIME,STUFF(STUFF(STUFF(STUFF(@t,9,0,' '),12,0,':'),15,0,':'),18,0,'.'))

I read something about loss of precision at the millisecond level, though. So there is still some need for research on the topic.

If you need the precision of the milliseconds, you can use DATETIME2 and just do:

SELECT CONVERT(DATETIME2,STUFF(STUFF(STUFF(STUFF(@t,9,0,' '),12,0,':'),15,0,':'),18,0,'.'))

I just read a great article regarding the precision. It says:

If you want to store the exact same value you had in DATETIME, just choose DATETIME2(3), you get the same precision but it only takes 7 bytes to store the value instead of 8.

Here is the link for more information.

Upvotes: 1

Related Questions