Add date without exceeding a month

Question

I hope someone could help me on this.

I want to add a month to a database date, but I want to prevent two jumping over month on those days at the end.

For instance I may have:

Jan 31 2009

And I want to get

Feb 28 2009

and not

March 2 2009

Next date would be

March 28 2009

Jun 28 2009

etc.

Is there a function that already perform this kind of operation in oracle?

EDIT

Yeap. I want to copy each month all the records with some status to the next ( so the user don't have to enter again 2,000 rows each month )

I can fetch all the records and update the date manually ( well in an imperative way ) but I would rather let the SQL do the job.

Something like:

insert into the_table 
select f1,f2,f3, f_date + 30 /* sort of ... :S */  from the_Table where date > ? 

But the problem comes with the last day.

Any idea before I have to code something like this?

for each record in 
   createObject( record )
   object.date + date blabala
   if( date > 29 and if februrary and the moon and the stars etc etc 9 

end 

update....  et

EDIT:2

Add months did the trick.

now I just have this:

insert into my_table
select f1, add_months( f2, 1 ) from my_table where status = etc etc 

Thanks for the help.

Answer 1

Oracle has a built-in function ADD_MONTHS that does exactly that:

SQL> select add_months(date '2008-01-31',1) from dual;

ADD_MONTHS(
-----------
29-FEB-2008

SQL> select add_months(date '2008-02-29',1) from dual;

ADD_MONTHS(
-----------
31-MAR-2008