interesting problem and solution - NUMTOYMINTERVAL() function
- From: "Fedock, John \(KAM.RIC\)" <John.Fedock@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Aug 2007 11:54:11 -0400
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
Other related posts:
