TO_DATE in QUERY with null year

I want to do this in a single query where I check if my creation date is bigger then another but the table columns was divide out by year, month and day and sometimes there a illegal values in year.

Select count{*) from coverage cv
WHERE EXIST (Select * from addressTable ad 
and cv.date > to_date(ad.year||ad.month||ad.day, 'YYYYMMDD')

When I run this I get the error ORA-01841: Full year must be between -4713 and +9999 and not be 0.

Can I write something in the query to ignore the illegal values and just count the legal ones.

Answer

Can I write something in the query to ignore the illegal values and just count the legal ones?

If you are running Oracle 12.2 or higher, you can use on conversion error:

to_date(ad.year || ad.month || ad.day default null on conversion error, 'yyyymmdd')

Advertisement

Answer