| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: add_months - Is it a bug or a feature?
A copy of this was sent to nothrow_at_hotmail.com
(if that email address didn't require changing)
On Fri, 13 Aug 1999 12:52:16 GMT, you 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
it is not a bug, it is the documented way it works. Your posting points out that you have a preference as to the way you feel it should work. Your way (to me) is as inconsistent and arbitrary as you feel the current implementation is.
>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
And that is what we give you.
One month from a boundary condition is one month later on the same boundary value..
If I started at 1-dec-xx, should next month be 1-jan-xx or 31-dec-xx. It should be 1-jan-xx of course.
Well, then if the next month after the first day of a month is the first day of the next month, the next month after the last day of the month is the last day of the next month.
>the adverse situation. I want '30-DEC-????' when I add a month to
>'30-NOV-????'.
>
why? (retorical question -- you just do. some (lots) of people want the last day of the next month)...
anyway... What is next month anyhow? 28 days to some people, same day next month to others (unless its jan-30 -- then we feb 28/29, but not march), for others, the last day of the next month if I am currently on the last day of the current month.
Lets say you get paid 2 times a month. Once on the 15'th and once on the last day. Add_months() starts to make sense now.
>Anyone know a way I can manipulate add_months to get this result?
>
no, but you can implement logic to get it.
>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;
>
decode or an if statement can do this:
declare
X date default '1-oct-1999';
begin
for y in (
select decode( sign( to_char( X, 'dd' ) -
to_char(last_day(add_months(X,1)),'dd')), -1,
to_date(to_char(x,'dd')||to_char(add_months(x,1),'-mon-yyyy')),
add_months(x,1) ) dt
from dual ) loop
dbms_output.put_line( y.dt );
end loop;
that decode says if the difference between the current day and the last_day of the next month is NEGATIVE then I want the same day of the next month, else I want the last day of the next month.
>Paul Gentile
>Consultant - Cardinal Group International. Inc.
do you know Cris Peterson?
>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.
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 13 1999 - 16:35:28 CDT
 |
 |