Changing data type from Char to Datetime

Thanks in adance

Platform: SQL 2000

A SQL table has a field named "pay-day" with Char(8) data type.

I tried to change its data type to datetime but only with an error message like this. I did right-click the table and tried to modify a data type.

- Unable to modify table.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Jay

This means that some of your data in that column cannot be implicitly converted to a datetime value.

Here is some more info on CAST and CONVERT -- you may want to try using these functions specifically to help identify which data is out of range.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Hope that helps

-Steve

|||

Being that all of the data in your table is valid date data:

It seems that your field (8) has a date like mmyydd if this is correct,

It would seem that SQL thinks this is a number which it want's to convert to a date.

Create new field and write a stored proceidure to update the new field to your field converted to date.

format your field using format(yourField,"mmyydd") then insert into the new date field.

When you convert SQL has to know what date format you are starting from.

|||

The conversion is not working because some of the data has non-conforming values -values that do NOT convert to valid dates.

You will need to locate the non-conforming values and correct them first. The following query should help you find them.

SELECT [Pay-Day]
FROM MyTable
WHERE isdate( [Pay-Day] ) = 0