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
This will give you a list of all columns in yours TableA
that have a date associated 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 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:
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
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.
Mark
source to share
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.
source to share
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?
source to share