oracle add_months function differs from Java

Question

I recently discovered a difference between Oracle adds months to a given date (using ADD_MONTHS function) and the way Java adds months to a Calendar object.

For instance in oracle:

select add_months('2009-02-28', +1) from dual;

produced the result: "09-03-31"

And the query:

select add_months('2009-02-28', -1) from dual;

Produces the result "09-01-31"

However in Java, the results of the same calculations (using GregorianCalendar.add() method) are (respectively): 09-03-28 and 09-01-28

Is there some way to make Oracle and Java behave the same? (e.g. some setting in oracle or some parameter in Java)?

Answer 1

When you do month arithmetic you need to decide, from the business point of view, what is the right way to deal with months with differing numbers of days. The flip side to the issue you raised is what happens when going from a longer month, like August, to a shorter one like February (and even from Feb to Feb if leap years are involved). If you are happy for errors to be reported because the calculation cannot find 'Feb-30', then look at INTERVAL