CODEWITHSUNDEEP
sqlsql-server
wkstar
wkstar

Reputation: 265

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

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?

Upvotes: 2

Views: 7543

Answers (4)

HLGEM
HLGEM

Reputation: 96562

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.

Upvotes: 2

marc_s
marc_s

Reputation: 754468

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

Upvotes: 2

Spence
Spence

Reputation: 29332

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?

Upvotes: 0

Evernoob
Evernoob

Reputation: 5561

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.

Upvotes: 1

Related Questions