sql - 2020-02-29减去一年导致错误ORA-01839 : date not valid for month specified

Question

我有一个简单的查询:

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

我认为结果应该是 2019-02-28，但是 oracle 抛出错误如下:

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

Answer 1

即the documented behaviour ;它甚至以此为例:

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

替代方法是使用 add_months(..., -12) ( docs )，这不会出错:

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

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

但请注意它是如何处理一个月中不同天数的；如果您要倒退整整一年，这并不是真正的问题，但仍有一些需要注意的事项:

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.

因此，其中一些可能没有达到您的预期:

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