Interval Insanity

A colleague of mine has had some problem with using the INTERVAL datatype within date operations within some production code.

I believe that it is recently deployed code and it being the 31 August today, a production problem was highlighted.

The fundamental issue has been highlighted elsewhere previously, for example at oracle-wtf and asktom (of course).

I’m sorry, but this is cr4p.

Ladies and Gentlemen, I give you an interval of one month, and add it to yesterday, the 30th August 2007:

Add one month to the 30th August 2007 and you get 30th September 2007. Unsurprisingly.

Connected to:
Oracle Database 10g Express Edition Release - Production
SQL> select to_date('30/08/2007','DD/MM/YYYY') dt1
2 , to_date('30/08/2007','DD/MM/YYYY') + interval '1' month dt2
3 from dual;

--------- ---------
30-AUG-07 30-SEP-07

Add one month to 31st August 2007 and you’d expect to get, or at least I’d expect to also get, 30th September 2007. But no, you get an invalid date.

SQL> ed
Wrote file afiedt.buf
1 select to_date('31/08/2007','DD/MM/YYYY') dt1
2 , to_date('31/08/2007','DD/MM/YYYY') + interval '1' month dt2
3* from dual
SQL> /
, to_date('31/08/2007','DD/MM/YYYY') + interval '1' month dt2
ERROR at line 2:
ORA-01839: date not valid for month specified

It seems that INTERVAL functionality has been implemented to what is allegedly the letter of the ANSI standard but with what is surely an obvious flaw … madness.


Get every new post delivered to your Inbox.

Join 62 other followers