add_months problem...

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.

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).

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.

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