Re: interesting problem and solution - NUMTOYMINTERVAL() function
- From: "Tim Hall" <timhall1@xxxxxxxxx>
- To: John.Fedock@xxxxxxxxxxxx
- Date: Thu, 30 Aug 2007 12:36:06 -0700
The same thing happened here yesterday. I wasn't even aware that some of
our former developers had used this function, in cases just like yours where
ADD_MONTHS was perfectly appropriate (and a lot easier to read!). Oh well,
it was a one-line fix and a good chance to exercise our emergency change
request procedures ;)
This was on 9.2.0.6 and 9.2.0.8 by the way. The code in question didn't
happen to be used on our 10g instances so we didn't find out if the bug
existed there too.
Regards,
Tim Hall
On 8/30/07, Fedock, John (KAM.RIC) <John.Fedock@xxxxxxxxxxxx> wrote:
>
> 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:
