SQL Server: "Conversion failed when converting datetime from character string."

Question

I'm trying to copy the results from a view to a table using:

insert into tableA 
  select * from viewB order by id;

I get the error message -

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

TableA and ViewB have about 80 fields. Trying to figure out which one's might have a type mismatch will take forever. How can I determine what column is causing the error quickly?

Answer 1

They mostly DATETIME fields? Looks like it's trying to shove a string into a DATETIME so you can start by looking at the first few of those.

Shouldn't take too long.

Answer 2

As a debug capability you might want to perform the select query from the view then push them down in a single insert into tableA.

Then it will throw an error containing which column couldn't be inserted. Assuming you can do this manually. Other than that, is there any way you can avoid having a string as your datetime column?

Answer 3

This would give you a list of all columns in your TableA that have a date-related type (DATE, DATETIME, DATETIME2, DATETIMEOFFSET etc.):

SELECT *
FROM sys.columns
WHERE object_id = object_id('TableA')
AND system_type_id IN (40, 42, 43, 58, 61)
ORDER BY name

You could run this on both tables and see if you find any discrepancies.

Or you could run this on both tables - it will give you a list of columns (names) and their associated datatype:

SELECT
    c.NAME,
    t.name 
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE object_id = object_id('TableA')
ORDER BY c.name

Maybe you'll see a column in TableB that's of type varchar that's being mapped to a column of type DATETIME in TableA and then find your culprit that way.

Marc

Answer 4

You have bad data in one of your fields. Check each of the fields that should contain dates with the isdate() function.

Select idfield, mydatefield from mytable where isdate(mydatefield) = 0

You will see the records which contain invalid dates. This is one reason why it is never a good idea to store dates as string data.