Search code examples
javaoracle

oracle add_months function differs from Java

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)?

Solution

  • 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).