Query column for data that cannot be converted to datetime

Question

I have an older table where a datetime column was built as a varchar and the form inserting data into this table was freeform with no validation/restrictions. I have corrected the form and now am set upon the task of normalizing the data. I need to query this column for all records that cannot be CAST as a datetime

Examples:

9/30/2008
9/30/13
august 2010
5/11
30SEP12
31-Mar-2011
2005

I have already cleaned up the N/A data to be NULL but am unsure of an efficient way of getting everything that would not be considered a valid date. Any hints or tricks or am I going to have to go through record by record to convert this column to a datetime data type?

Answer 1

You can use ISDATE to determine records that aren't dates:

SELECT *
FROM YourTable
WHERE isdate(OldDateField) = 0

You can use the query below to see which ones won't be dates:

SELECT isdate('9/30/2008')
SELECT isdate('9/30/13')
SELECT isdate('august 2010')
SELECT isdate('5/11')
SELECT isdate('30SEP12')
SELECT isdate('31-Mar-2011 2005')