oracle - 安全的 INTERVAL 算术

Question

此查询有效，没有错误

select add_months(date '2011-01-31', 1) from dual;

，而这个:

select date '2011-01-31' + interval '1' month from dual;

返回

ORA-01839: date not valid for month specified

那么有没有安全的方法可以使用INTERVAL文字添加间隔？

Answer 1

这遵循 ANSI 指定的向日期添加 INTERVAL 的行为¹。这也被记录在案 here :

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;

函数ADD_MONTHS另一方面，如果结果月份的天数较少，则只会给您该月的最后一天 - 我相信这个函数是为了解决这个问题而创建的。

<小时/>

¹ http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

b) Arithmetic is performed so as to maintain the integrity of
  the datetime data type that is the result of the <datetime
  value expression>. This may involve carry from or to the
  immediately next more significant <datetime field>. If the
  data type of the <datetime value expression> is TIME, then
  arithmetic on the HOUR <datetime field> is undertaken modulo
  24. If the <interval value expression> or <interval term> is
  a year-month interval, then the DAY field of the result is
  the same as the DAY field of the <datetime term> or <datetime
  value expression>.

c) If, after the preceding step, any <datetime field> of the
  result is outside the permissible range of values for the
  field or the result is invalid based on the natural rules for
  dates and times, then an exception condition is raised: data
  exception-datetime field overflow.