I have a date field that I need to cast to datetime. However when I do isdate on that field a get a few hundred rows that are like 2/31/2009 it's in the format I want just it obviously isn't a real date. I tried creating the code below to subtract 3 days from the date, but I'm still getting the error The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Is there a different way to do this?
select case when isdate(DateOpened) = 1 then cast(DateOpened as datetime)
else datediff(d,DateOpened, - 3) end as InvoiceDate