0

I have a column with date string in yyyy-mm-dd format. How can I retrieve all rows which have a date <= 90 days. The date string is null for some of the entries. Some of those date strings are also not in the correct format. This is what I tried.

select * from MY_TABLE where b.ASSESSMENT_DATE IS NOT NULL AND (trunc(sysdate) - TO_date(b.ASSESSMENT_DATE, 'yyyy-mm-dd')) <= 90;

But it is giving me an error 

ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range
yalkris
  • 2,596
  • 5
  • 31
  • 51
  • A `date` column does not have a format. A `varchar2` column does. I'm guessing that you mean that you have a `varchar2` column with data that is mostly in the format "yyyy-mm-dd" but occasionally in other formats. What do you want to do with the data that is in an invalid format? Do you want to ignore it? Or do you want to apply a different format mask to do the conversion? – Justin Cave May 08 '15 at 17:59
  • Exactly. I will modify my question. I want to ignore invalid formats. – yalkris May 08 '15 at 18:01

0 Answers0