Import Dates, Skip Bad Data By Using ISDATE() And CASE

You have a table with a 'date' column and it's stored as varchar, the problem is that you also have bad data in there
You want to import this data into another table, but if the data can not be converted into a date you want to make it NULL
The way to handle this is by using the ISDATE() function together with CASE


--Let's create our table with bad and good data
CREATE TABLE SomeFakeDateTable (FakeDate VARCHAR(23))
INSERT SomeFakeDateTable VALUES ('ababababa')
INSERT SomeFakeDateTable VALUES ('20060101')
INSERT SomeFakeDateTable VALUES ('20060299')
INSERT SomeFakeDateTable VALUES (NULL)
INSERT SomeFakeDateTable VALUES ('20060401')
INSERT SomeFakeDateTable VALUES ('20050331')


--Here is the query
SELECT FakeDate,
CASE
WHEN ISDATE(FakeDate) = 1 THEN CONVERT(DATETIME,FakeDate)
ELSE NULL
END TheRealDate
FROM SomeFakeDateTable