The Oracle DB function „add_months“ adds a given number of months to a given date:
SQL> select add_months(to_date('04.03.14','dd.mm.yy'),1) as result from dual;
RESULT
--------
04.04.14
Nice. But what about 2014-02-28 plus 1 month?
SQL> select add_months(to_date('28.02.14','dd.mm.yy'),1) as result from dual;
RESULT
--------
31.03.14
Ups. It turns out that (quote from documentation)
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.
The day component of the result can even be greater than the day component of the argument date:
SQL> select add_months(to_date('28.02.14','dd.mm.yy'),120) as result from dual;
RESULT
--------
29.02.24
This was the root cause of defect in a partner system. Of course, there is already a discussion on Stackoverflow over this issue.
As an alternative, you can use interval arithmetic:
SQL> select to_date('28.02.14','dd.mm.yy') + INTERVAL '10' YEAR as result from dual;
RESULT
--------
28.02.24
Nice. But interval arithmetic is not of much help because it does not work with days that does not exist in every month.
SQL> select to_date('28.02.14','dd.mm.yy') + INTERVAL '10' YEAR as result from dual;
select to_date('28.02.14','dd.mm.yy') + INTERVAL '10' YEAR as result from dual
*
ERROR in Zeile 1:
ORA-01839: date not valid for month specified
Date arithmetic is not trivial, I guess.