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>
