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

From the Oracle reference on add_months (http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm)

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. Otherwise, the result has the same day component as date.

That means you're going to have to write a method for Java that performs the same check to get the same results (or a function in PL/SQL that behaves the same as Java).

Answer 2

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

Answer 3

You could write your own add months function in java.

public Date functionAddMonth(Date d, int month)
{
    Calendar c = Calendar.getInstance();
    c.setTime(d);
    c.add(Calendar.DAY, 1);
    c.add(Calendar.MONTH, month);
    c.add(Calendar.DAY, -1);
    return c.getTime();
}