This is the way it is "supposed" to work. ANSI defined how interval math is supposed to be done and this is how they said it should work.

I've written about this in my current book:


<quote src=Expert Oracle Database Architecture>
In general, when using the Oracle DATE type I recommend the following:

* Use the NUMTODSINTERVAL built-in function to add hours, minutes, and seconds.
* Add a simple number to add days.
* Use the ADD_MONTHS built-in function to add months and years.

I do not recommend using the NUMTOYMINTERVAL function. The reason has to do with how the functions behave at the months’ end.

The ADD_MONTHS function treats the end of month days specially. It will in effect “round” the dates for us—if we add one month to a month that has 31 days and the next month has fewer than 31 days, ADD_MONTHS will return the last day of the next month. Additionally, adding one month to the last day of a month results in the last day of the next month. We see this when adding one month to a month with 30 or fewer days:

ops$tkyte@ORA10G> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.

ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
29-feb-2000 00:00:00 31-mar-2000 00:00:00

ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
28-feb-2001 00:00:00 31-mar-2001 00:00:00

ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
30-jan-2001 00:00:00 28-feb-2001 00:00:00

ops$tkyte@ORA10G> select dt, add_months(dt,1)
2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
30-jan-2000 00:00:00 29-feb-2000 00:00:00

See how the result of adding one month to February 29, 2000, results in March 31, 2000? February 29 was the last day of that month so ADD_MONTHS returned the last day of the next month. Additionally, notice how adding one month to January 30, 2000 and 2001 results in the last day of February 2000 and 2001, respectively.

If we compare this to how adding an interval would work, we see very different results:

ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
3 /
DT DT+NUMTOYMINTERVAL(1
-------------------- --------------------
29-feb-2000 00:00:00 29-mar-2000 00:00:00

ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
3 /
DT DT+NUMTOYMINTERVAL(1
-------------------- --------------------
28-feb-2001 00:00:00 28-mar-2001 00:00:00

Notice how the resulting date is not the last day of the next month, but rather the same day of the next month. It is arguable that this behavior is acceptable, but consider what happens when the resulting month doesn’t have that many days:

ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
3 /
select dt, dt+numtoyminterval(1,'month')
*
ERROR at line 1:
ORA-01839: date not valid for month specified

ops$tkyte@ORA10G> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
3 /
select dt, dt+numtoyminterval(1,'month')
*
ERROR at line 1:
ORA-01839: date not valid for month specified

In my experience, that makes using a month interval in date arithmetic impossible in general. A similar issue arises with a year interval: adding one year to February 29, 2000, results in a runtime error as well, because there is no February 29, 2001.
</quote>