August 31, 2007 7 Comments
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.
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.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - 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;
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.
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
, 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.