See the results of below queries:
>> SELECT ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR'),-4) FROM DUAL;
30-NOV-10
>> SELECT ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4) FROM DUAL;
31-MAR-11
How can I get ’30-MAR-11′ when adding 4 months to some date?
Please help.
Advertisement
Answer
There is another question here about Oracle and Java
It states that
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.
So I guess you have to manually check stating day and ending day to change the behaviour of the function. Or maybe by adding days instead of months. (But I didn’t find a add_day function in the ref)