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
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
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