Very Handy
October 16, 2002 - 1am Central time zone
Reviewer: Jim
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
another one on add_months
November 17, 2005 - 4am Central time zone
Reviewer: Vijay from India
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
Followup November 17, 2005 - 8am Central time zone:
add_months, as documented and by definition, returns the last_day of the resulting month if you
input the last_day of a month to begin with.
add_months( any_last_day_of_a_month, some_number_of_months) returns the last_day of the resulting
month.
I don't know what you mean in the last part of your paragraph, why doesn't this work?
here's the example
November 17, 2005 - 8am Central time zone
Reviewer: Vijay from India
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
Followup November 17, 2005 - 8am Central time zone:
but it is putting in feb 29th. What is special about the 28th?
You have something on the last day of a MONTH, add months says "add a month to the last day of a
month, you get the last day of the resulting month"
An alternative method....
November 17, 2005 - 10am Central time zone
Reviewer: John Scott from UK
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
Followup November 18, 2005 - 7am Central time zone:
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"
add_months is right approach
November 17, 2005 - 5pm Central time zone
Reviewer: Oraboy from MI, USA
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)
Followup November 18, 2005 - 10am Central time zone:
this is the way ANSI specified intervals to work, unless the standard changes, it'll be that way.
good learning
November 17, 2005 - 8pm Central time zone
Reviewer: Muhammad Ibrahim from Hongkong
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.
Followup November 18, 2005 - 10am Central time zone:
interval is ANSI, it is part of the sql standard.
add_months is Oracle, we implemented the feature years and years ago, very much before the interval
type.
followup the my query of missing 28th.
November 17, 2005 - 11pm Central time zone
Reviewer: Vijay from India
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
Followup November 18, 2005 - 10am Central time zone:
you don't, not with add months.
you'd have to "do it yourself" or use an interval (but beware of the cautions above about it
failing on days that are 29, 30, 31 easily.
seems like you are answering the questions and followups I am waiting for your help on this
November 18, 2005 - 7am Central time zone
Reviewer: vijay from India
Followup November 18, 2005 - 3pm Central time zone:
gee, sorry? I mean, come on. I do them in the order I see them.
Amusing ...
November 18, 2005 - 8am Central time zone
Reviewer: Greg from Toronto
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 ... :(
Followup November 18, 2005 - 3pm Central time zone:
A month is ambigious.
It is not like "a week"
Tell me, *what is a month*
A year even is ambigous - years do not have the same number of days.. If someone tells you "I am
exactly 12 years and 2 months old" today - tell me how many days old they are?
The missing 28th
November 18, 2005 - 8am Central time zone
Reviewer: Ralf from Germany
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
Followup November 18, 2005 - 3pm Central time zone:
well, if he always wants the same day - interval 1 year, just beware of the CAVEAT with days 29,
30, 31.
To the poster - wondering how to get 28th feb
November 18, 2005 - 9am Central time zone
Reviewer: Oraboy from MI, USA
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
Followup November 18, 2005 - 3pm Central time zone:
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.
I should have made it clear
November 18, 2005 - 4pm Central time zone
Reviewer: Oraboy from MI,USA
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
Followup November 19, 2005 - 9am Central time zone:
select to_date( '28-feb-' || to_char(2007+rownum,'fm9999'), dd-mon-yyyy
from whatever....
To VIjay
November 18, 2005 - 9pm Central time zone
Reviewer: Raj
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-F
eb-2005','dd-Mon-yyyy'),36)-1) from dual
/
duh..My bad
November 21, 2005 - 10am Central time zone
Reviewer: Oraboy from MI, USA
"
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.
something more on this.
November 29, 2005 - 6am Central time zone
Reviewer: vijay from India
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
Followup November 29, 2005 - 10am Central time zone:
select trunc(sysdate) + rownum-1
from all_objects
where rownum <= 365 (or 366, whichever you want)
And yet another
November 30, 2005 - 9am Central time zone
Reviewer: Borkur from Brussels, Belgium
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
how to add days to date
June 16, 2007 - 3am Central time zone
Reviewer: Ahmad Farghaly from Kuwait
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
|