SQL Date Math and the Leap Year



  • I had my best ever last friday ... (The 29 February 2008)



    Tested on Oracle 9.



    SELECT TO_DATE('2008-02-29','YYYY-MM-DD')+NUMTOYMINTERVAL(12, 'MONTH') AS dt FROM DUAL;

    ... Ends with an "ORA-01839: date not valid for month specified"



    SELECT ADD_MONTHS(TO_DATE('2008-02-29','YYYY-MM-DD'),12) AS dt FROM DUAL;

    ... Gives the expected result.



    So so lousy.



    PS. you can play with one year interval as well:

    SELECT TO_DATE('2008-02-29','YYYY-MM-DD')+NUMTOYMINTERVAL(1, 'YEAR') AS dt FROM DUAL ;

    ... Ends with an "ORA-01839: date not valid for month specified" too.



  •  Surely it's not just Oracle that does that, I know I started using +365 for that very reason.



  • Ack!  Don't do that!  One Year != Exactly 365 days!

    <FONT color=#0000ff size=2></FONT><FONT size=2>SELECT sysdate + 365 Today, SYSDATE + (365*5) IsThisFiveYearsFromNow,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

           SYSDATE + NUMTOYMINTERVAL(60, 'MONTH') OrIsThisFiveYearsFromNow

      FROM dual<o:p></o:p>

     

    </FONT>

Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.