Safe INTERVAL arithmetic

Question

This query works withour errors

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

, while this one:

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

returns

ORA-01839: date not valid for month specified

So is there any safe way to add interval using INTERVAL literal?

Answer 1

This follows ANSI-specified behavior¹ of adding INTERVALs to dates. This is also documented 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;

The function ADD_MONTHS on the other hand will just give you the last day of the month if the resulting month has less days - and I believe this function was created to address this issue.

---

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