MacLochlainns Weblog

You could write an ugly SQL statement full of CASE statements to derive the leap year moving forward or backward in time. You could likewise write an uglier statement that allows for moving back from a 31 day month to a 30 day month or forward from a 30 day month to a 31 day month.

However, you can use the add_months(date_in,number_of_months), like:

Leap year back to a non-leap year (system clock set to 29-FEB-2008):

SQL\> SELECT add_months(SYSDATE,-12) FROM dual;

From the last day of May to the last day of April (system clock set to 31-MAY-2008):

SQL\> SELECT add_months(SYSDATE,-1) FROM dual;

From the last day of April to the last day of May (system clock set to 30-APR-2008):

SQL\> SELECT add_months(SYSDATE,1) FROM dual;

You can read more here in the Oracle SQL Reference.

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.