Database Reference
In-Depth Information
DT ADD_MONTHS(DT,1)
-------------------- --------------------
29-feb-2000 00:00:00 31-mar-2000 00:00:00
EODA@ORA12CR1> select dt, add_months(dt,1)
2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
28-feb-2001 00:00:00 31-mar-2001 00:00:00
EODA@ORA12CR1> select dt, add_months(dt,1)
2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
30-jan-2001 00:00:00 28-feb-2001 00:00:00
EODA@ORA12CR1> select dt, add_months(dt,1)
2 from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
3 /
DT ADD_MONTHS(DT,1)
-------------------- --------------------
30-jan-2000 00:00:00 29-feb-2000 00:00:00
See how the result of adding one month to February 29, 2000, results in March 31, 2000? February 29 was the last
day of that month so
ADD_MONTHS
returned the last day of the next month. Additionally, notice how adding one month
to January 30, 2000 and 2001 results in the last day of February 2000 and 2001, respectively.
If we compare this to how adding an interval would work, we see very different results:
EODA@ORA12CR1> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
3 /
DT DT+NUMTOYMINTERVAL(1
-------------------- --------------------
29-feb-2000 00:00:00 29-mar-2000 00:00:00
EODA@ORA12CR1> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
3 /
DT DT+NUMTOYMINTERVAL(1
-------------------- --------------------
28-feb-2001 00:00:00 28-mar-2001 00:00:00
Notice how the resulting date is not the last day of the next month, but rather the
same
day of the next month. It
is arguable that this behavior is acceptable, but consider what happens when the resulting month doesn't have that
many days:
EODA@ORA12CR1> select dt, dt+numtoyminterval(1,'month')
2 from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
3 /
select dt, dt+numtoyminterval(1,'month')
*