SQL Server: "Conversion failed while converting date and time from character string."

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

insert into tableA 
  select * from viewB order by id;


I am getting an error -

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 might have a type mismatch will take forever. How can I determine which column is causing the error?


source to share

4 answers

This will give you a list of all columns in yours TableA

that have a date associated type (DATE, DATETIME, DATETIME2, DATETIMEOFFSET, etc.):

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


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

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

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


You might see a column in TableB that is of type varchar that maps to a column of type DATETIME in Table A, and then find your culprit that way.




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

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


You will see entries containing invalid dates. This is one of the reasons it is never recommended to store dates as string data.



Mostly DATETIME fields? It looks like it's trying to drag and drop a row into DATETIME so you can start by looking at the first few of them.

It doesn't take too long.



As a debug feature, you can execute a select query from a view and then insert them in a single insert into a table.

Then it throws an error that this column cannot be inserted. Assuming you can do it manually. Other than that, is there a way to avoid the string being like a datetime column?



All Articles