Leap year not welcome by the TO_YMINTERVAL function
Playing around with illustrating date math, I noticed that Oracle 11g still hasn’t fixed the TO_YMINTERVAL
function. The function should let you to subtract months, years, or a combination of month and years from any date. However, it doesn’t like leap years or going from the last day of a longer month to the last day of a shorter month.
You can add or subtract a year from a date in the middle of the month:
SELECT TO_DATE('15-JAN-08') - TO_YMINTERVAL('01-00') FROM dual; |
You can also add or subtract a month from a date in the middle of the month:
SELECT TO_DATE('15-FEB-08') - TO_YMINTERVAL('00-01') FROM dual; |
You can’t subtract a year from the last day of February in a leap year. There is also no logic to let you add a year to 28-FEB-07 and get 29-FEB-08 (like a Boolean for last_day). Likewise, you can’t add a month to the last day of a month where the next month has fewer days in it; or subtract a month from the last day of a month where the last day is greater than the prior months last day without raising an ORA-01839
error.
Here’s what happens subtracting a year from this years leap day:
SELECT TO_DATE('29-FEB-08') - TO_YMINTERVAL('01-00') FROM dual * ERROR at line 1: ORA-01839: DATE NOT valid FOR MONTH specified |
You can’t complain about it though, it’s apparently compliant with the SQL92 standard according to metalink.