SQL Server DateTime conversion failure

Question

I have a large table with 1 million+ records. Unfortunately, the person who created the table decided to put dates in a varchar(50) field.

I need to do a simple date comparison -

datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

But it fails on the convert():

Conversion failed when converting datetime from character string.

Apparently there is something in that field it doesn't like, and since there are so many records, I can't tell just by looking at it. How can I properly sanitize the entire date field so it does not fail on the convert()? Here is what I have now:

select count(*)
from MyTable
where
    isdate(lastUpdate) > 0
    and datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

---

@SQLMenace

I'm not concerned about performance in this case. This is going to be a one time query. Changing the table to a datetime field is not an option.

@Jon Limjap

I've tried adding the third argument, and it makes no difference.

---

@SQLMenace

The problem is most likely how the data is stored, there are only two safe formats; ISO YYYYMMDD; ISO 8601 yyyy-mm-dd Thh:mm:ss:mmm (no spaces)

Wouldn't the isdate() check take care of this?

I don't have a need for 100% accuracy. I just want to get most of the records that are from the last 30 days.

---

@SQLMenace

select isdate('20080131') -- returns 1
select isdate('01312008') -- returns 0

---

@Brian Schkerke

Place the CASE and ISDATE inside the CONVERT() function.

Thanks! That did it.

Answer 1

Place the CASE and ISDATE inside the CONVERT() function.

SELECT COUNT(*) FROM MyTable
WHERE
    DATEDIFF(dd, CONVERT(DATETIME, CASE IsDate(lastUpdate)
        WHEN 1 THEN lastUpdate
        ELSE '12-30-1899'
    END), GetDate()) < 31

Replace '12-30-1899' with the default date of your choice.