Data Type Conversion

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.

Can anyone help on this issue?

Thanks in advance.

Neil.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.

Regards,

hmscott|||My first approach would be:

select *
from YourTable
where IsDate(YourTextDate) = 0

That should show you all the rows with invalid dates.

blindman|||Thanks!

I think I've solved the problem now.

Some of the dates are 2003 and some are 03 so if I change the 03's to 2003 and then convert the data type it works!

Thanks anyway!|||Oh, ugh. I better go back to the SQL BOL. I thought IsDate was only a VBScript function.

Sigh. So much to learn, so little time.

Thanks,

hmscott|||There's a lot of functions and tools out there. I frequently see people reference usefull things on this forum that I have to go look up.

It's too bad that when a new version comes out the help system doesn't have a section titled "Just the stuff you don't already know."

blindman