CODEWITHSUNDEEP
sqlsql-servert-sql
CShrad
CShrad

Reputation: 3

CONVERT not working for VARCHAR to DATETIME

I've tried everything I can find (CAST, CONVERT, converting on import, checking default language settings) but I cannot find a working solution to convert my VARCHAR values to DATETIME. I'd like to insert a conversion of this field into a new table where that column is set up as DATETIME.

When I run: 

SELECT CONVERT(VARCHAR, [first air date], 121) 
FROM mytable

the results are returned fine and error free. However, when I run:

INSERT INTO newtable
    SELECT ID, column1, column2, 
           CONVERT(VARCHAR, [first air date], 121) 
    FROM mytable

I get this error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

Why am I getting results (as in the first query) but it won't insert them into another table?

The data looks like this: '2004-02-23 00:00:00.000' (I know the dashes aren't best practice but I have no wiggle room on that because this format has been determined by other colleagues).

I'm importing the data via .csv and this column is set as date format in Excel.

Correction I now realise it should be CONVERT(DATETIME, [first air date], 121). However, this does not solve the actual problem at hand, which is converting to DATETIME from VARCHAR without the 'out of range' error.

Upvotes: 0

Views: 1277

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

First, always include the length of a varchar for a conversion. Second, you really want to convert to DATETIME, which is the destination type.

Third, you can get the non-convertible values using:

SELECT [first air date]
FROM mytable
WHERE TRY_CONVERT(DATETIME, [first air date], 121) IS NULL

You can then figure out how to handle the values that cannot be converted.

Upvotes: 3

Related Questions