RE: interesting problem and solution - NUMTOYMINTERVAL() function

Tom Kytes elaborates on this issue in his book oracle expert database
architecture around page 527.   It is indeed how the function works...


 

Some quotes from Tom's book:

 

"It is arguable that this behavior is acceptable.... But ..."  [then
considers the below issue] ...  "In my experience, that makes using a
month interval in date arithmetic impossible in general."

 

He also notes:   "A similar issue arises with a year interval:  adding
one year to February 29, 2000, results in a runtime error as well,
because there is no February 29, 2001".

 

se la vie,

Joel Patterson 
Database Administrator 
joel.patterson@xxxxxxxxxxx 
x72546 
904  727-2546 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Hall
Sent: Thursday, August 30, 2007 3:36 PM
To: John.Fedock@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: interesting problem and solution - NUMTOYMINTERVAL()
function

 

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: