SQL Server - Get all records with invalid date format

Question

In existing database there a field CreatedOn varchar(10) which contains some invalid dates like 99/99/9999 and many more.

I want to change all the invalid dates to 1/1/1990, so I can convert this column to datetime by using

ALTER TABLE xyzAlter Column CreatedOn DateTime

but it is giving error:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime 
   data type resulted in an out-of-range value.

Is there any way to get all the records which are not in correct date time format?

Answer 1

try this:

select * from yourTable WHERE ISDATE(yourColumn)!=1

see: ISDATE(), it returns 1 if the expression is a valid date, time, or datetime value, otherwise it returns a 0.

EDIT to update then alter your column try this:

--table name: xyz
--column name: CreatedOn varchar(10) to datetime

UPDATE xyz
    SET CreatedOn ='1990/01/01'
    WHERE ISDATE(CreatedOn)!=1
GO
ALTER TABLE xyz Alter Column CreatedOn DateTime
GO