Adding or Subtracting Time from a DATE-Datatypes
A question I am frequently asked is, “How do I add time to or subtract time from a DATE type?” For example, how do you add one day to a DATE, or eight hours, or one year, or one month, and so on. There are three techniques you’ll commonly use:
•\ Simply add a NUMBER to the DATE. Adding 1 to a DATE is a method to add one day. Adding 1/24 to a DATE therefore adds one hour, and so on.
•\ You may use the INTERVAL type, as described shortly, to add units of time. INTERVAL types support two levels of granularity: years and months or days/hours/minutes/seconds. That is, you may have an interval of so many years and months or an interval of so many days, hours, minutes, and seconds.
•\ Add months using the built-in ADD_MONTHS function. Since adding a month is generally not as simple as adding 28 to 31 days, a special purpose function was implemented to facilitate this.
Table 12-3 demonstrates the techniques you would use to add (or subtract, of course) N units of time to a date.
Table 12-3. Adding Time to a Date
Table 12-3. (continued)
In general, when using the Oracle DATE type, I recommend the following:
•\ Use the NUMTODSINTERVAL built-in function to add hours, minutes,and seconds.
•\ Add a simple number to add days.
•\ Use the ADD_MONTHS built-in function to add months and years.
I do not recommend using the NUMTOYMINTERVAL function (to add months and years).
The reason has to do with how the functions behave at the months’ end.
The ADD_MONTHS function treats the end of month days specially. It will, in effect, round the dates for us—if we add one month to a month that has 31 days and the next month has fewer than 31 days, ADD_MONTHS will return the last day of the next month. Additionally, adding one month to the last day of a month results in the last day of the next month. We see this when adding one month to a month with 30 or fewer days:
$ sqlplus eoda/foo@PDB1
SQL> alter session set nls_date_format = ‘dd-mon-yyyy hh24:mi:ss’; Session altered.
SQL> select dt, add_months(dt,1) from (select to_date(’29-feb-2000′, ‘dd-mon-yyyy’) dt from dual );DT ADD_MONTHS(DT,1)
SQL> select dt, add_months(dt,1) from (select to_date(’28-feb-2001′, ‘dd-mon-yyyy’) dt from dual );DT ADD_MONTHS(DT,1)
SQL> select dt, add_months(dt,1) from (select to_date(’30-jan-2001′, ‘dd-mon-yyyy’) dt from dual );DT ADD_MONTHS(DT,1)
SQL> select dt, add_months(dt,1) from (select to_date(’30-jan-2000′, ‘dd-mon-yyyy’) dt from dual );DT ADD_MONTHS(DT,1)
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:
SQL> select dt, dt+numtoyminterval(1,’month’) from (select to_date(’29-feb- 2000′,’dd-mon-yyyy’) dt from dual);DT DT+NUMTOYMINTERVAL(1
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:
SQL> select dt, dt+numtoyminterval(1,’month’) from (select to_date(’30-jan-2001′,’dd-mon-yyyy’) dt from dual);select dt, dt+numtoyminterval(1,’month’)
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL> select dt, dt+numtoyminterval(1,’month’) from (select to_date(’30-jan-2000′,’dd-mon-yyyy’) dt from dual);select dt, dt+numtoyminterval(1,’month’)
ERROR at line 1:
ORA-01839: date not valid for month specified
In my experience, this makes using a month interval in date arithmetic impossible in general. A similar issue arises with a year interval: adding one year to February 29, 2000, results in a runtime error because there is no February 29, 2001.