Interval Issues

Date functions have been ubiquitous within our database for years. For the most part date calculations are robust and can solve many problems.

Oracle even caters for a drift noted in the 8th century fixed in the 16th century - due to some issues with the dates chosen for Easter by the catholic church.

select date '1582-10-04' + 1 gregory from dual;

GREGORY
-------------------
15-10-1582 00:00:00

Let's take for instance one method of adding one month to a given day.
Here I add a month to the last day of August:

select add_months(last_day(date '2009-08-01'), 1) end_of_sept from dual;

END_OF_SEPT
-------------------
30-09-2009 00:00:00

And I safely get the last day of September (which has one less day).

Likewise, the documentation states:

For example, the MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.

So these three expressions will return slightly different results

select months_between(last_day(date '2008-02-01'), (date '2008-02-01')) is_28_days
      ,months_between(last_day(date '2009-08-01'), (date '2009-08-01')) is_30_days
      ,months_between(last_day(date '2009-09-01'), (date '2009-09-01')) is_29_days
from dual;

IS_28_DAYS IS_30_DAYS IS_29_DAYS
---------- ---------- ----------
.903225806 .967741935 .935483871

However if you're working with intervals here is a little trap to watch out for.

select last_day(date '2009-08-01')
+ INTERVAL '1' MONTH end_of_sept
from dual;

+ INTERVAL '1' MONTH int
*
ERROR at line 2:
ORA-01839: date not valid for month specified

Interestingly if you subtract one month interval from the end of September you get 30th August. Similar behaviour occurs when subtracting from a leap February. It seems an interval of one month is considered as 31 days, but it can't allow properly for smaller months.
The same error will occur however if you attempt to subtract from a month such as July.

select last_day(date '2009-09-01')
 - INTERVAL '1' MONTH end_of_aug
from dual;

END_OF_AUG
-------------------
30-08-2009 00:00:00

The 11g documentation states:

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error...
SELECT TO_DATE('31-AUG-2004','DD-MON-YYYY') + TO_YMINTERVAL('0-1') FROM DUAL;
...
The first fails because adding one month to a 31-day month would result in September 31, which is not a valid date.

Personally I can't quite grasp why there should be a difference between interval arithmetic and functions such as months_between, but just be aware if you need to be pedantic with your dates.

Perhaps this behaviour with intervals will be modified in a future release?