MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘to_yminterval’ tag

Leap year, last day of the month the easy way?

with one comment

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.

Written by maclochlainn

May 29th, 2008 at 5:29 pm

Leap year not welcome by the TO_YMINTERVAL function

without comments

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.

Written by maclochlainn

May 27th, 2008 at 6:05 pm