Re: add_months - Is it a bug or a feature?

From: Chris Colclough <chris.colclough_at_jhuapl.edu.nospam>
Date: Fri, 13 Aug 1999 13:25:19 -0400
Message-ID: <37B454FF.10439403@jhuapl.edu.nospam>

Save the following as a .sql file and see if this does what you want:

undefine date
undefine nmonths

select decode(

         sign (to_number(to_char(to_date('&&date'),'DD')) -
       to_number(to_char(add_months(to_date('&&date'),&&nmonths),'DD'))),
       -1,
       add_months(trunc(to_date('&&date')),&&nmonths) +
         to_number(to_char(to_date('&&date'),'DD')),
       add_months(to_date('&&date'),&&nmonths))

from dual
/

nothrow_at_hotmail.com wrote:

> 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 - 12:25:19 CDT