Add date without exceeding a month.

Question

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

I think you're looking for LAST_DAY:

http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_functions_2006.htm

Answer 2

I think you'll have to write it on your own, My advice is first to evaluate the "last day of the month" with this method:

• Add one month (not 30 days, one month!)
• Find first day of the month (should be easy)
• substract one day

Then compare it to your "plus x days" value, and choose the lowest one (I understood the logic behind the jump from 31/Jan to 28/Feb, but I don't get it for the jump from 28-Feb to 28-Mar)

Answer 3

It sounds like you want the current month plus one (with appropriate rollover in December)

and the minimum of the last day of that month and the current day.

Answer 4

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

Answer 5

I just did: select add_months(TO_DATE('30-DEC-08'), 2) from dual

and got 28-FEB-2009

No need to use LAST_DAY. If you went that route, you could create a function that: 1. takes a date 2. Changes the day to the first of the month. 3. Add a month. 4. Changes the day to the LAST_DAY for that month.