I have a SQL Server 200 database and I need to change the data types in a few fields in a table.
The fields are currently are NVARCHAR and need to be datetime
I get an error message when trying to do this:
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
My regional settings show as English UK and date format of dd/mm/yyyy.
The obvious suggestion (and therefore probably wrong) would be to identify rows where the nvarchar data is not actually a valid date time.
I'm not a purist when it comes to cursors. My first approach would be to use a cursor. Scroll through the records and attempt to insert them (just the date/time column) into a new table. If you encounter an error, output sufficient data to clearly identify a row.
Remember that it takes only a single bad data point to trash the conversion process. There may not be that many bad data points.
Something else to remember about what SQL is doing in the background: it is actually creating a new temporary table with the date/time column, inserting the data, then dropping the old table and renaming the new temporary table to the old table name. Just food for thought.