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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment