Hi there,

I have a table which contains 7 million records, and I want to find the ones with invalid date.

The date field is char(8). Instead of doing something like

substr(CLAIM_INJURY_DATE, 1, 4) between '0001' and '9999'
and substr(CLAIM_DATE, 5, 2) between '01' and '12'
and substr(CLAIM_DATE, 7, 2) between '01' and '31'
... (I have not checked Feb 29, Sep 31.. etc yet)

Is there any easy and quick way to select the invaild date?

Thanks,
Jinse

how about date(colname) this will check if character string can be migrated to date. use also substr and concat to get the correct input format
substr(colname,1,4)||'-'|| ....... --> 2006-12-31

If the date is invalid, am I able to catch the error in date(colname)? So I can write something like:

select case when date(colname) = ? then 'Invalid'
when date(colname) = ?? then 'Valild'
end as Indicator