Search code examples
sqlsql-serverdatetimevarchar

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
Solution

  • 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