How To Convert String To Datetime Without Seperator Using Sql?

How to convert this string to Datetime 20180227105954636241 select CONVERT(datetime, '20180227105954636241', 120) Conversion failed when converting date and/or time from characte

Solution 1:

According to your question, I can suggest use SUBSTRING Method and parse NVARCHAR values and then convert it to DATETIME.

DECLARE@date NVARCHAR(MAX) ='20180227105954636241'DECLARE@year NVARCHAR(4) =SUBSTRING(@date,0,5)
DECLARE@month NVARCHAR(2) =SUBSTRING(@date,5,2)
DECLARE@day NVARCHAR(2) =SUBSTRING(@date,7,2)
DECLARE@hours NVARCHAR(2) =SUBSTRING(@date,9,2)
DECLARE@minutes NVARCHAR(2) =SUBSTRING(@date,11,2)
DECLARE@seconds NVARCHAR(2) =SUBSTRING(@date,13,2)
DECLARE@milliseconds NVARCHAR(2) =SUBSTRING(@date,15,3)

SELECTCONVERT(DATETIME, (@Year+'-'+@month+'-'+@day+' '+@hours+':'+@minutes+':'+@seconds+'.'+@milliseconds), 120)

Solution 2:

You can't convert these patterned string to datetime directly and i don't see any use of CONVERT function for this purpose. You can just format the string like below to cast it :

DECLARE@dateVARCHAR(MAX) ='20180227105954636241'DECLARE@date1VARCHAR(30) 

set@date1=SUBSTRING(@date,  1, 8) +' '+-- this is datepartSUBSTRING(@date,  9, 2) +':'+-- this is hourSUBSTRING(@date, 11, 2) +':'+-- this is minuteSUBSTRING(@date, 13, 2) +'.'+-- this is secondSUBSTRING(@date, 15, 6)          -- this is decimal of secondselectcast(@date1as datetime2(6))  -- 6 is the decimal point of second

Output

2018-02-27 10:59:54.636241

Solution 3:

You can use the STUFF function to insert the necessary colons, periods, and dashes. STUFF takes 4 parameters. First is the string to stuff chars into, second is the position within the string, third is the number of chars to delete (always 0 in this example), fourth is to char to insert.

After stuffing, this results in a string 26 chars long but the convert to datetime only accepts 23 chars. You have to take the LEFT 23 of this value, cutting off the last 3 chars of the string to convert to the datetime datatype.

DECLARE@tempvarchar(100) ='20180227105954636241'SELECTCONVERT(datetime, LEFT(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(@temp, 15, 0, '.'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 7, 0, '-'), 5, 0, '-'), 23), 120)