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

4 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.

Marc Blum said...

Hi, a few months ago I raised a TAR/SR regarding this issue. But then we found out, that ORACLE just does, what the SQL standard says: adding 1 month incremets the value of the month attribute of the date. If it gives an invalid date, the database should raise an exception.

To me, this is no good definition. We had to define and code our own DATE/TIMESTAMP arithmetics to get a behaviour which reflects the feel and use of human beings about and of date arithmetics.

Gary Myers 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
/

Brian 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.