Interval Insanity
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.
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 10.2.0.1.0 - ProductionSQL> 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;
DT1 DT2
--------- ---------
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.
OraStory 
Yep, don’t like it. Especially when:
select add_months(’31-AUG-07′,1,) from dual;
gives you what you’d expect…
It would be nice if it would give null or some obvious default instead of giving an error. Not knowing the requirements your colleague had, I’m not throwing stones – just showing an alternative. If the add_months() function is used, you get the “correct” results:
SQL> select trunc(sysdate) dt1,
2 add_months(trunc(sysdate), 1) dt2
3 from dual;
DT1 DT2
——— ———
31-AUG-07 30-SEP-07
At the very least, this is another reminder to all us coders to test for the exceptions and unexpected and not just test to see if it works.
Thanks for your comments, guys.
Sure, add_months is just fine. I believe that the idea of the interval thing was to add some flexibility with some day to second intervals as well in a flexible pre-10 bespoke job scheduling “framework”.
I’m not sure to what extent you would or should expect to test the boundaries of built-in functionality – tricky one that, not sure either way.
It re-enforces the caveat of always being careful with new features, I suppose.
The problem isn’t using new functionality, it is using it without learning about it first.
ADD_MONTHS has its own inconsistencies. Adding a month to 30th Sept doesn’t give 30th Oct, for example.
Personally, I prefer the approach with INTERVAL where it throws an exception. At least that way you are forced to think exactly what YOUR requirement is.
Thanks for your valid point, Gary.
Nice catch, I will update my add-months article over at Pythian so people know about this fun as well.
Pingback: Pythian Group Blog » Oracle Gotcha: months_between 31-days-per-month assumption