ADD_MONTHS versus INTERVAL

Here is some examples of using the INTERVAL function, basically take the current date and minus 5 days, months and years.

SQL> select sysdate "Current date", 
sysdate - interval '5' day "INTERVAL"  
from dual;

Current d INTERVAL
--------- ---------
14-SEP-09 09-SEP-09

SQL> select sysdate "Current date", 
sysdate - interval '5' month "INTERVAL"  
from dual;

Current d INTERVAL
--------- ---------
14-SEP-09 14-APR-09

SQL> select sysdate "Current date", 
sysdate - interval '5' year "INTERVAL"  
from dual;

Current d INTERVAL
--------- ---------
14-SEP-09 14-SEP-04

How about if we try the following:

SQL> select to_date('31-AUG-2009','DD-MON-YYYY') - interval '6' month 
from dual;

select to_date('31-AUG-2009','DD-MON-YYYY') - interval '6' month 
from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified

The 31st of August minus 6 months would make it February 31st which doesn't exist. This is the ANSI standard!

SQL> select to_date('28-AUG-2009','DD-MON-YYYY') - interval '6' month from dual;

TO_DATE('
---------
28-FEB-09

The correct way:

SQL> select add_months(to_date('31-AUG-2009'),-6) 
from dual;

ADD_MONTH
---------
28-FEB-09

Because ADD_MONTHS will round the date to the maximum date for that month.