2020-02-29 minus one year caused error ORA-01839: date not valid for month specified

Viewed 195

I have a simple query as:

select to_date('2020-02-29', 'yyyy-mm-dd') - interval '1' year from dual

I think the result should be 2019-02-28, but oracle throws error as:

Error report -
ORA-01839: date not valid for month specified

1 Answers

That is the documented behaviour; it even gives this as an example:

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error. For example, the next two statements return errors:

SELECT TO_DATE('31-AUG-2004','DD-MON-YYYY') + TO_YMINTERVAL('0-1')
  FROM DUAL;

SELECT TO_DATE('29-FEB-2004','DD-MON-YYYY') + TO_YMINTERVAL('1-0')
  FROM DUAL;

The first fails because adding one month to a 31-day month would result in September 31, which is not a valid date. The second fails because adding one year to a date that exists only every four years is not valid. However, the next statement succeeds, because adding four years to a February 29 date is valid:

SELECT TO_DATE('29-FEB-2004', 'DD-MON-YYYY') + TO_YMINTERVAL('4-0')
  FROM DUAL;

TO_DATE('
---------
29-FEB-08

The alternative is to use add_months(..., -12) (docs), which won't error:

select add_months(date '2020-02-29', -12) from dual;

ADD_MONTHS
----------
2019-02-28

But note how that deals with different number of days in the month; not really an issue when you're going back exactly a year, but still something to be aware of:

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month.

So some of these might not do what you expected:

with rcte (dt) as (
  select last_day(date '2020-01-01')
  from dual
  union all
  select last_day(trunc(dt, 'MM') + interval '1' month)
  from rcte
  where dt < date '2020-06-01'
)
select dt,
  add_months(dt, -12) as minus12, add_months(dt, -3) as minus3, add_months(dt, -1) as minus1,
  add_months(dt, 1) as plus1, add_months(dt, 3) as plus3, add_months(dt, 12) as plus12
from rcte
order by dt;

DT         MINUS12    MINUS3     MINUS1     PLUS1      PLUS3      PLUS12    
---------- ---------- ---------- ---------- ---------- ---------- ----------
2020-01-31 2019-01-31 2019-10-31 2019-12-31 2020-02-29 2020-04-30 2021-01-31
2020-02-29 2019-02-28 2019-11-30 2020-01-31 2020-03-31 2020-05-31 2021-02-28
2020-03-31 2019-03-31 2019-12-31 2020-02-29 2020-04-30 2020-06-30 2021-03-31
2020-04-30 2019-04-30 2020-01-31 2020-03-31 2020-05-31 2020-07-31 2021-04-30
2020-05-31 2019-05-31 2020-02-29 2020-04-30 2020-06-30 2020-08-31 2021-05-31
2020-06-30 2019-06-30 2020-03-31 2020-05-31 2020-07-31 2020-09-30 2021-06-30
Related