add_months - Is it a bug or a feature?

add_months behaves like this when you add 1 month to these dates.

before after

-----------   ------------
28-FEB-1991   31-MAR-1991
28-FEB-1992   28-MAR-1991
29-FEB-1992   31-MAR-1991
30-NOV-????   31-DEC-????

What add_months does is to determine if the original day is the last day of the month and if it is, it takes the liberty of assuming that you want the last day of the next month. This is usually true *if* the next months last day is less than your current month's. Who would want a
'1-DEC-????' result from adding a month to '31-OCT-????'? No one, of
course! We would all expect '30-NOV-????'. But, I certainly don't want the adverse situation. I want '30-DEC-????' when I add a month to
'30-NOV-????'.
Anyone know a way I can manipulate add_months to get this result?

This doesn't work - "add_months( (my_date - 1), 1 ) + 1" which subtracts a day and adds a day before and after the month addition.

The best I've come up with is a serious "kludge". (NOTE: I have not debugged the following code)

original_date_day = to_date( mydate, 'dd' ); new_date = add_months( mydate, 1 );
if( to_date( new_date, 'dd' ) > original_date_day )

     select to_date( original_day_date||to_char(new_date,'-MON-yyyy',
'dd-MON-yyyy') into modified_date from dual;

Paul Gentile
Consultant - Cardinal Group International. Inc. paul_at_cardinalgroup.com (508) 620-9119 x170

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 13 1999 - 07:52:16 CDT