Find invalid dates in SQL Server 2008

Question

I have a 300.000 rows table; one of the columns is a varchar() but it really contains a date xx/xx/xxxx or x/x/xxxx or similar. But executing the following test yields an error:

SELECT CAST(MyDate as Datetime) FROM MyTable

The problem is that it doesn’t tell me in which row…

I have executed a series of “manual” updates by trial an error and performed simple updates to fix those, but there’s got to be some weird values that need to either be deleted or fixed.

For example I performed a simple test that fixed about 40 rows:

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 quite a few weird rows that had dates like 01/01/0003 and such. (Dates range from 1998 to 2010).

However, I’d like to know which rows are failing in the above select.

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

Answer 1

SELECT
    *
FROM
   MyTable
WHERE
    ISDATE(MyDate) = 0

Answer 2

Did you try the ISDATE function?

Answer 3

Careful with IsDate. I have 1 bad record in a table of thousands. It says 8201-11-30 is a valid date. IsDate should have a YEAR limitation.