SQL> SELECT DATE '2006-01-31' + INTERVAL '1' MONTH
2 FROM dual;
SELECT DATE '2006-01-31' + INTERVAL '1' MONTH
*
ERROR at line 1:
ORA-01839: date not valid for month specified
Friday, February 24, 2006
Stop Press: Oracle Granted License To Extend February
Yes, it's official. Oracle has been granted permission to extend February by 3 days. Shame no-one told the developers responsible for INTERVAL arithmetic.
Subscribe to:
Post Comments (Atom)
3 comments:
I've written about that before - this is why I'll continue to use add_months.
Oracle did not do this....
The ANSI/ISO committee specified this exact behavior. We just implemented the standard.
It was an unfortunate decision I believe, one that basically removes the ability to safely do interval arithmetic like this.
I'm not convinced the result of
SELECT add_months(DATE '2006-02-28',1) from dual;
is intuitive either.
I like that
SELECT (DATE '2006-02-28' + INTERVAL '1' MONTH) from dual;
provides an alternative.
You can really confuse the issue by combining the two....
SELECT CASE WHEN TO_CHAR(ADD_MONTHS(DATE '2006-02-28',1),'DD') <=
TO_CHAR(DATE '2006-02-28','DD') THEN ADD_MONTHS(DATE '2006-02-28',1)
ELSE DATE '2006-02-28' + INTERVAL '1' MONTH END new_date1,
CASE WHEN TO_CHAR(ADD_MONTHS(DATE '2006-01-31',1),'DD') <=
TO_CHAR(DATE '2006-01-31','DD') THEN ADD_MONTHS(DATE '2006-01-31',1)
ELSE DATE '2006-01-31' + INTERVAL '1' MONTH END new_date2
from dual
/
It's rather frightening, that Oracle would allow "NEW" functions to introduce such new bugs rather than reduce old ones.
I was able to write a test SQL in about 8 minutes (including formatting & testing) to validate the possible failures vs ADD_MONTHS.
What is interesting is the different values picked between add_months & INTERVAL as well.
Post a Comment