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.

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

3 comments:

Thomas Kyte said...

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.

SydOracle said...

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
/

Unknown said...

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.