Thread: Data conversion error
05-18-04, 18:09 #1Registered User
- Join Date
- Mar 2004
Unanswered: 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?
05-18-04, 19:10 #2Registered User
- Join Date
- Apr 2004
- Kansas City, MO
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
When life gives you a lemon, fire the DBA.