Sunday, March 11, 2012

Data conversion error

I'm importing data from a text table, into a temp table, and then on to a final working table. I'm running into difficulty converting data from text into a 'datetime' format. I'm getting the following error:

"Arithmetic overflow error" when trying to convert into a column with the data type "DateTime"

I half expected it to reject all conversions into a Date format because of the source file being in text format, but it allows this conversion in other columns.

If I switch the Data type to 'nvarchar' instead of 'datetime' it converts and pops up with a date format.

My question is: Will this nvarchar that looks like a date behave like a date? For example, if someone tries to perform a calculation with a date format, will the nvarchar suffice, or would it cause problems?

Any ideas?It won't convert what's causing this error. You need to do something like this:

SELECT CASE WHEN ISDATE(text_field) = 0 THEN '01/01/01' ELSE CAST(text_field AS DATETIME) END
FROM table

No comments:

Post a Comment