CODEWITHSUNDEEP
sqlsql-serverdatetimevarchar
Ajwad Ibrahim
Ajwad Ibrahim

Reputation: 127

converting varchar to datetime in sql

currently in my DB i have

20181015 151706 ---------- varchar(15)
2018-10-15 15:17:06 000--- Datetime

how do i convert this from varchar to datetime ?

i have tried using this command below

SELECT CONVERT(Datetime,CREATE_TIME , 120) from TABLE

but im getting error

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

SELECT CONVERT(Datetime,CREATE_TIME , 120) from TABLE

Upvotes: 2

Views: 171

Answers (2)

codejockie
codejockie

Reputation: 10864

There's a little issue with your date string. It works when 20181015 151706 is changed to just the date part 20181015.

SELECT CONVERT(DATETIME, col)
FROM (
   select '20181015' col
) t1

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

The error caused by your special 20181015 151706 varchar DateTime value,

you can try to use substring function make the DateTime format string value then do convert.

SELECT CONVERT(DATETIME,CONCAT(substring(col, 1, 4),'-',substring(col, 5, 2),'-',substring(col, 7, 2),' ',substring(col, 9, 2),':',substring(col, 11, 2),':',substring(col, 13, 2)))
FROM (
   select REPLACE('20181015 151706',' ','') col
) t1

sqlfiddle

Upvotes: 1

Related Questions