Finding invalid dates in SQL Server 2008

Question

I have a table of 300,000 rows; one of the columns is varchar (), but it does contain the date xx / xx / xxxx or x / x / xxxx or similar. But doing the following test gives an error:

SELECT CAST(MyDate as Datetime) FROM MyTable 

The problem is that it does not tell me which line ...

I did a series of “manual” updates by trial error and performed simple updates to fix them, but theres got some weird values ​​that need to be either deleted or fixed.

For example, I performed a simple test that captured about 40 lines:

 UPDATE MyTable SET MyDate = REPLACE(MyDate, '/000','/200') FROM MyTable WHERE MyDate like ('%/000%') UPDATE MyTable SET MyDate = REPLACE(MyDate, '/190','/199') FROM MyTable WHERE MyDate like ('%/190%') 

This fixed a lot of weird strings that had dates like 01/01/0003, etc. (Dates range from 1998 to 2010).

However, Id likes to know that which do not work in the above list.

What would be the best way to print them so that I can delete, edit, or see what to do? Thanks.

Answer 1

 SELECT * FROM MyTable WHERE ISDATE(MyDate) = 0 

Answer 2

Have you tried the ISDATE function?

Answer 3

Caution with IsDate. I have 1 bad entry in the thousands table. He says that 8201-11-30 is a valid date. IsDate must have a YEAR constraint.

Answer 4

ISDATE does not seem to always work.

the following returns 1 in SQL Server 2008 R2 Select ISDATE('04- December 20')

But an attempt to reset the same value today will fail. Select cast('04- December 20' as date)

Conversion error while converting date and / or time from character string.