First impressions to get the next year might be
to add 365 days... with leap years that sure to bite you
at some stage, and who wants to get into a what a leap year
debate.

Add_months is cool

Thanks Tom

Dear Tom,
good day to you, hope you are fine and doing good, I am facing one small issue with add_months below is the scenario.

below query returns 27/02/2008 which is fine
select add_months(to_date('27/02/2005','dd/mm/yyyy'),36) from dual;

but if i change the query to
select add_months(to_date('28/02/2005','dd/mm/yyyy'),36) from dual
it returns 29/02/2008, I want to know why 28/02/2008 is missed,
basically I am working on a script which selects max date from a column of table and for each day of next 3 years inserts the records in the table if there's a leap year between the day is missed, how can this be rectified.

Kind Regards,
Vijay

Hi Tom,
here's the example

INSERT INTO CV
SELECT TRANSCO_REGION, ADD_MONTHS(CALENDAR_DAT,12), VALUE FROM CV
WHERE calendar_dat <= (SELECT MAX(calendar_dat) FROM CV)
AND calendar_dat > (SELECT ADD_MONTHS(MAX(calendar_dat),-12) FROM CV)
UNION ALL
SELECT TRANSCO_REGION, ADD_MONTHS(CALENDAR_DAT,24), VALUE FROM CV
WHERE calendar_dat <= (SELECT MAX(calendar_dat) FROM CV)
AND calendar_dat > (SELECT ADD_MONTHS(MAX(calendar_dat),-12) FROM CV)
UNION ALL
SELECT TRANSCO_REGION, ADD_MONTHS(CALENDAR_DAT,36), VALUE FROM CV
WHERE calendar_dat <= (SELECT MAX(calendar_dat) FROM CV)
AND calendar_dat > (SELECT ADD_MONTHS(MAX(calendar_dat),-12) FROM CV)
UNION ALL
SELECT TRANSCO_REGION, ADD_MONTHS(CALENDAR_DAT,48), VALUE FROM CV
WHERE calendar_dat <= (SELECT MAX(calendar_dat) FROM CV)
AND calendar_dat > (SELECT ADD_MONTHS(MAX(calendar_dat),-12) FROM CV)
UNION ALL
SELECT TRANSCO_REGION, ADD_MONTHS(CALENDAR_DAT,60), VALUE FROM CV
WHERE calendar_dat <= (SELECT MAX(calendar_dat) FROM CV)
AND calendar_dat > (SELECT ADD_MONTHS(MAX(calendar_dat),-12) FROM CV)
UNION ALL
SELECT TRANSCO_REGION, ADD_MONTHS(CALENDAR_DAT,72), VALUE FROM CV
WHERE calendar_dat <= (SELECT MAX(calendar_dat) FROM CV)
AND calendar_dat > (SELECT ADD_MONTHS(MAX(calendar_dat),-12) FROM CV)
UNION ALL
SELECT TRANSCO_REGION, ADD_MONTHS(CALENDAR_DAT,84), VALUE FROM CV
WHERE calendar_dat <= (SELECT MAX(calendar_dat) FROM CV)
AND calendar_dat > (SELECT ADD_MONTHS(MAX(calendar_dat),-12) FROM CV)

the above sql inserts into CV table records for next 7 years starting from max(calendar_dat) already in the table, what the problem I am facing is 2008 is leap year it misses the date 28/02/2008 similary for 2012.

I hope this example makes things a little clear for you.

Kind Regards,
Vijay

I've found that this works on 9.2.0.1 and later -

select sysdate + interval '1' year from dual

the interval can be a variety of types, e.g.

select sysdate + interval '3' day from dual

must be careful with that one.

ops$tkyte@ORA10GR2> select to_date('31-jan-2005') from dual;

TO_DATE('
---------
31-JAN-05

ops$tkyte@ORA10GR2> select to_date('31-jan-2005') + interval '1' month from dual;
select to_date('31-jan-2005') + interval '1' month from dual
                              *
ERROR at line 1:
ORA-01839: date not valid for month specified


ops$tkyte@ORA10GR2> select to_date('31-jan-2005') + interval '2' month from dual;

TO_DATE('
---------
31-MAR-05



I'm not fond of interval math too much - not with adding months anyway.  It takes the rather simplistic view of "it'll be the same day in the resulting month" - meaning adding months to anything on days 29, 30, 31 is potentially an "error" 

John 
 if you have Tom's latest book, there is in-depth details  by Tom , on when to use interval and when not to.

"add_months" is calendar sensitive, "interval" is not.

consider this-
SQL>alter session set nls_date_format='DD.MM.YYYY';
SQL>select to_date('10-01-2000', 'DD-MM-YYYY')+ interval '1' year from dual;

TO_DATE('1
----------
10.01.2001 -- this is fine

SQL>select to_date('29-02-2000', 'DD-MM-YYYY')+ interval '1' year from dual;
                                             *
ERROR at line 1:
ORA-01839: date not valid for month specified

SQL>select add_months(to_date('29-02-2000', 'DD-MM-YYYY'),12) from dual;
ADD_MONTHS
----------
28.02.2001

the error is because interval expects feb 29th in 2001..

(so atleast with the current versions releases, add_months is more reliable)

 

first of all this interval concept first time i am seeing it. thanks to the poster.

fine if it doesnt work for the below

select to_date('29-02-2000', 'DD-MM-YYYY')+ interval '1' year from dual;

then i have two questions:

1) what is the difference between adding interval and add_months?
2) what is the use oracle has provided this interval concept???

Regards,
Ibrahim.

Hi Tom,
good day to you, and thanks for taking the efforts and time to look into my problem, as you have mentioned
add months says "add a month to the last day of a month, you get the last day of the resulting month"
so my query example inserts 29th of Feb but I miss 28th of Feb how do I get that, here's some more details on it

select add_months(to_date('27/02/2007','dd/mm/yyyy'),12) from dual

output is 27/02/2008

select add_months(to_date('28/02/2007','dd/mm/yyyy'),12) from dual

output is 29/02/2008

how do I get 28/02/2008

thanking you in anticipation
Vijay

What I find really brain-wracking is this:

select add_months(add_months(to_date('30-jul-2005'), -1), 1) nd from dual;

ND
---------
31-JUL-05

1 row selected.

Forget about leap years and all for 1 second ...

date - 1 month + 1 month ... is not equal to date ??

You'd almost think that:

d + x - x = d

But that doesn't seem to hold here .. irritating date math ... Ouch .. my head ... :(

Vijay,

what about the following query?

select case extract(day from to_date('28/02/2007','dd/mm/yyyy'))
when 1 then add_months(to_date('28/02/2007','dd/mm/yyyy'),12)
else add_months(to_date('28/02/2007','dd/mm/yyyy')-1,12)+1
end
from dual;

CASEEXTRA
---------
01-MAR-13

It's a little bit more complicated but always returns 28/02 when you add a multiple of 12 to 28/02 of any year.

However, you get a perhaps unexpected result if you feed in a date like 29/02/2008!

Kind regards, Ralf

SQL>select add_months(to_date('28/02/2007','dd/mm/yyyy'),12) from dual;

ADD_MONTH
---------
29-FEB-08

SQL>-- I dont care of leap year..Just add 365 days and show me the result

SQL>select to_date('28/02/2007','dd/mm/yyyy')+365 from dual;

TO_DATE('
---------
28-FEB-08 

years are not 365 days.

ops$tkyte@ORA9IR2> select to_date('28/02/2007','dd/mm/yyyy')+365*rownum from all_users;

TO_DATE('
---------
28-FEB-08
27-FEB-09
27-FEB-10
27-FEB-11
27-FEB-12
26-FEB-13
26-FEB-14
26-FEB-15
26-FEB-16
25-FEB-17
25-FEB-18
25-FEB-19
25-FEB-20
24-FEB-21
24-FEB-22
24-FEB-23
24-FEB-24
23-FEB-25
23-FEB-26
23-FEB-27
23-FEB-28
22-FEB-29
22-FEB-30
22-FEB-31
22-FEB-32
21-FEB-33
21-FEB-34
21-FEB-35
21-FEB-36
20-FEB-37
20-FEB-38
20-FEB-39
20-FEB-40
19-FEB-41
19-FEB-42

35 rows selected.
 

Tom
  I  know adding 365 days is not right and I intended to explain to original poster (who wanted to jump from 28th Feb 2007 to 28th Feb 2008 ) was not calendar-friendly , rather plain arithmetic. (like adding n days)

I should have put it this way (explaining the poster he/she needs to instruct Oracle the logic they want to follow)


SQL>alter session set nls_date_format='dd.mm.yyyy';

Session altered.

SQL>ed
Wrote file afiedt.buf
  1* select last_day(add_months(to_date('28/02/2007','dd/mm/yyyy'), (12*rownum-1)))+28 from dba_users where rownum<10
SQL>/

LAST_DAY(A
----------
28.02.2008
28.02.2009
28.02.2010
28.02.2011
28.02.2012
28.02.2013
28.02.2014
28.02.2015
28.02.2016


or some other better way to implement the logic in SQL

 

How about query
select decode(to_char(to_date('28-Feb-2005','dd-Mon-yyyy'),'dd'),to_char(add_months(to_date('28-Feb-2005','dd-Mon-yyyy'),36),'dd'),add_months(to_date('28-Feb-2005','dd-Mon-yyyy'),36),add_months(to_date('28-Feb-2005','dd-Mon-yyyy'),36)-1) from dual
/

"
select to_date( '28-feb-' || to_char(2007+rownum,'fm9999'), dd-mon-yyyy from whatever....
"
- Duh..!! thats right

I dont know why I was trying to find a hard solution for a simple problem ( Guess I was thinking too much and my mind was preoccupied with date functions and overlooked the basic question.) :-)

PS: May be when you get a chance you should try to put this in your blog (if this sounds interesting).. how do you manage to keep your feet on ground (I mean basics).. I mean you read a lot, answer lots of questions and I am sure you would have more than one solution, for a given problem.

Hi Tom,
as you have said "well, if he always wants the same day - interval 1 year", I want to use the same day interval and I can't use the interval as it's 9i feature,my database is 8i, how can I populate a date column of table for all the days of a year using query starting from today's date

Kind Regards,
Vijay

here is a way, but the question remains what to do with the leap days that don't map in to the next year. Here I put 29.02 to null. If this would be run for a long (long) series we could map each leap day to the next leap day in about 4 years time.
SQL> create table toto  as
  2  select to_date('311299','DDMMRR')+rownum as datum from dba_source where rownum<3000;

Table created.
SQL> select datum,add_months(trunc(datum,'MONTH'),12)+
  2  case
  3  when to_char(datum,'DDMM')='2902' then null
  4  else to_number(to_char(datum,'DD')-1) end datum_yar
  5  from toto
  6* order by 1

DATUM     DATUM_YAR
--------- ---------
25-FEB-00 25-FEB-01
26-FEB-00 26-FEB-01
27-FEB-00 27-FEB-01
28-FEB-00 28-FEB-01
29-FEB-00
01-MAR-00 01-MAR-01
02-MAR-00 02-MAR-01
03-MAR-00 03-MAR-01
04-MAR-00 04-MAR-01
05-MAR-00 05-MAR-01
06-MAR-00 06-MAR-01

...

DATUM     DATUM_YAR
--------- ---------
18-FEB-03 18-FEB-04
19-FEB-03 19-FEB-04
20-FEB-03 20-FEB-04
21-FEB-03 21-FEB-04
22-FEB-03 22-FEB-04
23-FEB-03 23-FEB-04
24-FEB-03 24-FEB-04
25-FEB-02 25-FEB-03
26-FEB-02 26-FEB-03
27-FEB-02 27-FEB-03
28-FEB-02 28-FEB-03
01-MAR-02 01-MAR-03
02-MAR-02 02-MAR-03
 

Hi Tom, how to add days to a specified day
like add_daye ('1 1 2007', 8) = '9 1 2007'

is it implemented in Oracle,

Thanks

select add_months(to_date('28/feb/2007'),12)-
decode(mod(to_char(add_months(to_date('28/feb/2007'),12),'yyyy'),4),0,1,0) from dual

First, let me clear the confusion most people seem to have about Add_Months Function. The Add_Months definition includes "If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month." Most people miss out on the importance of the first clause and simply presume that it only applies to cases where the input "day of month" is greater than the "last day of the resulting month". However, there is no such restriction in the definition. Therefore, it could also apply to cases where the input "day of month" is the last day of the input month and is less than or equal to the result "day of month" which is the case in question here.

What you need is an expression or better yet a "Function" (imagine that) that simulates a modified Add_Months Function, one where the above definition is changed to simply "If the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month." Then, subtract one day from whatever the result is. By the way, the part before subtracting one day would also be the same definition as a VB/VBA DateAdd("m", NumMonthsInc, SourceDate) call.

To do that, you would need an expression like:

case
when
to_char(SourceDate, 'DD') > to_char(last_day(add_months(SourceDate, NumMonths)), 'DD')
then
add_months(SourceDate, NumMonths) - 1
else
to_date (
to_char(add_months(SourceDate, NumMonths), 'MM) + '/'
to_char(SourceDate, 'DD') + '/'
to_char(add_months(SourceDate, NumMonths), 'YYYY)
'MM/DD/YYYY')
)
- 1
end

If you wrote it into a Function, you could eliminate all those duplicate "add_months(SourceDate, NumMonths)" calls with a local variable not to mention embedding that monster of a Case Expression every time you needed it.

Perhaps I missed something in my hurry, but a function based on the next coding wordks for me:

declare
v_date date;
v_bool boolean := FALSE;
begin
v_date := to_date('&datum','DDMMYYYY');
if v_date = last_day(v_date) then
v_date := v_date - 1;
v_bool := TRUE;
end if;
v_date := add_months(v_date, &aantal);
if v_bool then
if v_date = last_day(v_date) then
null;
else
v_date := v_date + 1;
end if;
end if;
dbms_output.put_line(to_char(v_date, 'DDMMYYYY'));
end;

@Ron: Your function does solves the "bug" with the "design" of AddMonths not making the Result Day of Month = Source Day of Month when Source Date = Last Day of Source Date's Month and Result Date's Month has more Days than Source Date's Month. However:

a) It doesn't solve the Original Poster's Q, which is to make the Result Day of Month 1 Day less than the Source Date's Day of Month (even when not in end-of-month scenarios). To do that, you just need to subtract 1 from the result at the end of your Function (or one that calls your Function to do what the Poster needs).

b) Your Function's logic is unnecessarily complex. It uses 2 Local Var's, 3 If's, 1 Else and 5 Assign's. If you converted my Case Expression to a Function, it would just need 1 Local Var, 1 If, 1 Else and 3 Assign's. Also and therefore, it reads more clearly, if you were to translate it to a natural language (i.e if you needed to explain it to someone or someone were trying to understand what it's doing by reading it).

BTW, I say it's a "bug" with the "design" of the AddMonths Function, not just the Function because it's doing what its docs claim it was designed to do. However, I still think it's poor (or at least "un-useful") design because I suspect the most likely use is probably to calculate Expiration Date's of Insurance Policies and that's not how they (and I suspect also other contractual agreements with "months"-based terms) are normally calculated. A 12-month Policy Effective 2/28/2003 (@ 12:01 am) would Expire on 2/28/2004 (@ 12:01 am), not the 2/29/2004 AddMonths (2/28/2003, 12) would return. 2/28/2004 is also what would be returned by Excel's EDate, VB/VBA's DateAdd and .NET's DateTime.AddMonths. Of course, those are all in software produced by a little company in Washingtion. What do they know? ;)

@TOM CHIEN: You are right Tom. In my search for my own problem I failed to see the original quest.

Don't want to be the wise guy, but if I simplify my test script it would look like this:

begin
if Sourcedate = last_day(Sourcedate) then
Sourcedate := Sourcedate - 1;
Sourcedate := add_months(Sourcedate, NumMonths);
Sourcedate := Sourcedate + 1;
else
Sourcedate := add_months(Sourcedate, NumMonths);
end if;

-- Sourcedate :- Sourcedate - 1 ( the original quest )
end

This is clear and readable, is it not?

I cannot help wondering that I mis something. Something that you (clearly) have taken care of in your solution. If so, please help me out because I've deployed this solution.

The simplified coding works fine with positive addition. Working with negative values can cause unexpected results.

Coding should be:

begin
if Sourcedate = last_day(Sourcedate) then
Sourcedate := Sourcedate - 1;
Sourcedate := add_months(Sourcedate, NumMonths);
if Sourcedate ^= last_day(Sourcedate) then
Sourcedate := Sourcedate + 1;
end if;
else
Sourcedate := add_months(Sourcedate, NumMonths);
end if;

-- Sourcedate :- Sourcedate - 1 ( the original quest )
end

hi tom
my question is that i change the particular date in year.
example that..
select to_char( add_months(to_date('29-02-2000', 'DD-MM-YYYY'),12),'year') from dual;
result is
TO_CHAR(ADD_MONT
----------------
two thousand one
but my requirement is 2001.

  1  select to_char( add_months(to_date('29-02-2000', 'DD-MM-YYYY'),12),'yyyy')
  2* from dual
ops$tkyte%ORA11GR2> /

TO_C
----
2001