Re: add_months problem...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 05 Jan 1999 14:24:27 GMT
Message-ID: <36a21f0d.89535665@192.86.155.100>

A copy of this was sent to "Duncan Hodson" <duncan_at_tronze.demon.co.uk> (if that email address didn't require changing) On Tue, 5 Jan 1999 13:29:14 -0000, you wrote:

>Hi all you Oracle gurus,
>
>I have a problem with the add_months command. The command to demonstrate
>the problem is...
>
>select add_months('28-FEB-96', 1) from dual;
>
>If i add a month to '28-FEB-96' i get '28-MAR-96'. Fine because 1996 is a
>leap year.
>If i add a month to '29-FEB-96' i get '31-MAR-96'. Not good. I want
>'29-MAR-96'.
>If i add a month to '27-FEB-96' i get '27-MAR-96'. Fine.
>If i add a month to '28-FEB-97' i get '31-MAR-97'. Not good. I want
>'28-MAR-97'.
>
>Oracle's being too clever (again!) and is detecting that the 28th/29th
>February is the last day in that month so is giving me the last day of the
>next month.
>

Thats the documented behaviour of add_months tho, from the doc:

Syntax
ADD_MONTHS(d,n)

Purpose
Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.

>I want it to give me either the same day number in the next month, or if
>we're at '31-JAN-98' i want it to return '28-FEB-98' (the closest date to 31
>in the next month).
>

I think you get what you want with:

least( &d+(4*7+1), add_months(&d,1) )

where &d is your date. I tried all of your examples and got what you wanted:

SQL> @test
old 1: select least( &d+(4*7+1), add_months(&d,1) ) from dual new 1: select least( to_date('31-JAN-98')+(4*7+1), add_months(to_date('31-JAN-98'),1) ) from dual

LEAST(TO_

28-FEB-98 old 1: select least( &d+(4*7+1), add_months(&d,1) ) from dual new 1: select least( to_date('28-FEB-96')+(4*7+1), add_months(to_date('28-FEB-96'),1) ) from dual

LEAST(TO_

28-MAR-96 old 1: select least( &d+(4*7+1), add_months(&d,1) ) from dual new 1: select least( to_date('29-FEB-96')+(4*7+1), add_months(to_date('29-FEB-96'),1) ) from dual

LEAST(TO_

29-MAR-96 old 1: select least( &d+(4*7+1), add_months(&d,1) ) from dual new 1: select least( to_date('27-FEB-96')+(4*7+1), add_months(to_date('27-FEB-96'),1) ) from dual

LEAST(TO_

27-MAR-96 old 1: select least( &d+(4*7+1), add_months(&d,1) ) from dual new 1: select least( to_date('28-FEB-96')+(4*7+1), add_months(to_date('28-FEB-96'),1) ) from dual

LEAST(TO_

28-MAR-96
>Is there a way to return the date's i'm expecting, or am i gonna have to do
>some nasty date-to-string-then-fiddle-with-it processing to return the
>desired results? I'd rather not because i use add_months to calculate due
>dates on a monthly/quarterly/biannually basis and it's all contained
>in a neat single update statement.

You can use the LEAST function in the update where you currently have add_months only right now...

>
>Cheers muchly in advance.
>
>= Duncan Hodson : Analyst Programmer : Welcom Software : Harrogate UK
>= Work : dhodson_at_welcom.co.uk : www.welcom.co.uk
>= Home : dch@<remove_nospam>tronze.demon.co.uk : www.tronze.demon.co.uk
>
>
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jan 05 1999 - 08:24:27 CST