CODEWITHSUNDEEP
sqloracle-databasefunctiondatemath
OscarRyz
OscarRyz

Reputation: 199215

Add date without exceeding a month

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.

Upvotes: 2

Views: 688

Answers (5)

ericp
ericp

Reputation: 611

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.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

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

Upvotes: 11

Walter Mitty
Walter Mitty

Reputation: 18940

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.

Upvotes: 0

Philippe Grondier
Philippe Grondier

Reputation: 11138

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)

Upvotes: 0

diciu
diciu

Reputation: 29333

I think you're looking for LAST_DAY:

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

Upvotes: 2

Related Questions