Finding non valid dates with a char field

[timc]

I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below

select date_stopped from patient_medication
where isdate(convert(datetime,date_stopped)) = 1

This won't work, I get the
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

Any way around this?

[rmiao]

There are bad values, you may need check constraint on that column to ensure it gets good value.

[nosepicker]

select date_stopped from patient_medication
where isdate(date_stopped) = 0