SQL Update varchar to date error

Question

Hi

I have a table with 5 million records of dates stored as char(10) with format yyyy/mm/dd. I need to convert these to datetime, so I use:

 UPDATE [Database].[dbo].[Table]
    SET [DoB]=convert(datetime,[DoBText],103)
 GO

But I get the error:

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Now, I've tried fixing the data so this doesn't happed - i.e. no "yyyy" below 1900 or above 2000, no "dd" above 30 (28 for Feb), no "mm" above 12 or below 0. No NULLs.

Still the conversion fails.

Is there any way I can let SQL to skip the conversion on an error and just go on?

E.g. something like:

  SET [DoB]= try to do [DoB]=convert(datetime,[DoBText],103) if fails SET [DoB] = NULL

Thanks Karl

Answer 1

insert the date in yyyyMMdd format or use parameters and you won't have this problem. or do one of the following

SET DATEFORMAT DMY
SET DATEFORMAT MDY
SET DATEFORMAT YMD
SET DATEFORMAT YDM

Answer 2

The conversion style 103 handles a date in the format dd/mm/yyyy. Use the conversion style 111 instead to handle the format yyyy/mm/dd.

Answer 3

Try the following:

SET DATEFORMAT YMD;

UPDATE [Database].[dbo].[Table]
SET [DoB]=[DoBText]
WHERE ISDATE( [DoBText] ) = 1

This will convert those that are recognised as valid date/time values, the rest will be left as-is.