SQL Date Math and the Leap Year

Ying pointed out on my article about finding the last Saturday of the year that adding a year to a DATE datatype works great with syntax like this:

SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;

SYSDATE+N
---------
27-NOV-07

It works great right up until you hit February, 29:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtoyminterval(1, 'YEAR') from dual;
*
ERROR at line 1:
ORA-01839: date not valid for month specified

So what can we do about this? Well, as far as I can guess there are only two options. First, you could avoid ever using +numtoyminterval(1, 'YEAR') in your code and instead use +numtodsinterval(365, 'DAY') like this:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtodsinterval(365, 'DAY') from dual

TO_DATE('
---------
28-FEB-05

This may cause some confusion as 365 days after January 15, 2004 would be January 14, 2005 and 365 days after January 1, 2004 is actually December 31, 2004, but it should never throw an ORA- error.

UPDATE: Ying commented with this solution which is probably better in most scenarios. By using the add_months function we can step forward a number of months and Oracle will automatically truncate to the last day of the month if the resulting month has fewer days than the starting month.

SQL> SELECT Add_Months(to_date('02/29/2004', 'MM/DD/YYYY'), 12) from dual;

ADD_MONTH
---------
28-FEB-05

The other alternative is to handle the exception programmatically. Either avoid inserting a February 29 or avoid using the +numtoyminterval(1, 'YEAR') only when handling a February 29.

I’d say option 1 is the better choice, but both have their drawbacks.

This is the same error you get if you attempt to add a month to, say January 30th since there is never a February 30th.

SQL> select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual;
select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified

I guess this is why most things are good for “30 days” not “1 month”.

Thanks Ying for pointing out this anomaly.

database, oracle, date, to_date, timestamp, time, database administration, database development

4 thoughts on “SQL Date Math and the Leap Year”

  1. I discuss with my fren yesterday and come out a solution. Hope this will help.

    SELECT Add_Months(trunc(sysdate),12) FROM dual;

  2. Sorry. Leap years hit this one too.

    SQL> SELECT Add_Months(to_date('29-FEB-1999', 'DD-MON-YYYY'),12) FROM dual
    2 /
    SELECT Add_Months(to_date('29-FEB-1999', 'DD-MON-YYYY'),12) FROM dual
    *
    ERROR at line 1:
    ORA-01839: date not valid for month specified

    SQL>

    Cheers, APC

  3. Nice try APC, unfortunately 1999 did not have a 2/29 date:

    select to_date(’01-Mar-1999′,’DD-MON-YYYY’)-1 from dual

    returns 28-Feb-1999…

  4. That’s even better Ying! So for the leap year here’s the result:

    SQL> SELECT Add_Months(to_date('02/29/2004', 'MM/DD/YYYY'), 12) from dual;

    ADD_MONTH
    ---------
    28-FEB-05

    Thanks for sharing your solution.

Leave a Reply

Your email address will not be published. Required fields are marked *