Query column for data that cannot be converted to datetime

datetime, sql-server-2008, type-conversion

Solution

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')

Problem

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?

Original source