interesting problem and solution - NUMTOYMINTERVAL() function

Had an interesting problem (and solution) to pass along.  

When the day switched to *exactly* Aug 29, some of our EDI processing started to error off with "Oracle error ORA-01839: date not valid for month specified" errors.  

Same thing happened in our test systems. I'll spare you the 5 hours it took to track down the issue, but the cause was some old code was using the oracle provided function NUMTOYMINTERVAL() to calculate 6 months in the past.  

AUG 29, 2007 - 6 months, using this function produced FEB 29, 2007, which of course does not exist.  

A simple ADD_MONTHS(SYSDATE, (-6)) fixed the problem.          

John Fedock

"K" Line America, ISD Department  

--
http://www.freelists.org/webpage/oracle-l