Re: Possible Feb 29, 2000 Bug?

Jonathan Leffler <jleffler@informix.com> wrote: > On Tue, 11 Aug 1998, Larry Kemmerling wrote: > > While doing Y2K testing, our organization came across > > a problem trying to subtract a 1 year interval from a > > datetime column with the value "2000-02-29 00:00:00". Patient: "Doctor, it hurts when I do this." Doctor: "Don't do that." > > SELECT ( DateVal - INTERVAL (1) YEAR(9) TO YEAR ) > > FROM tFoo; > > > > -1267 The result of a datetime computation is out of range. > > You are mixing an INTERVAL YEAR TO MONTH value with a DATETIME value tha > includes DAY..FRACTION components, and the results of such computations are > unpredictable when you are working with the tail end of a month (any date > after the 28th in general). There is no 29th February 1999; the > computation for 1 year earlier than 29th February 2000 produces that date, > which is invalid. We can argue the toss over error numbers, but the > calculation is erroneous. > In my view, all such mixed mode calculations should be banned outright > because they are not unambiguous for all possible values, but they are > allowed and they cause this FAQ. Admittedly, this is a slightly different > spin on the standard question, but the net result is the same. Actually, ANSI *required* that all legal calculations be performed -- in other words, if your calculation is legal and would produce a legal result, it must be allowed. The simple fact is, any arithmetic with year-month intervals can produce invalid dates... for which the ANSI-prescribed behavior is to return an error. You can USE this fact by doing such tasks on a row-by-row basis, checking for error codes, then "fudging" the particular calculation in a way that makes sense for your application. > ** Don't mix calculations with intervals in the YEAR/MONTH ** > ** class with DATETIME values which include and components ** > ** from the DAY..FRACTION class. ** "YEAR/MONTH class" == "Year-month intervals" in ANSI. "DAY..FRACTION class" == "Day-time intervals" in ANSI. > No problem; just let your Informix Tech Support person know I've read you > the riot act and that this is a FAQ and the code in question is faulty. If > you need me to talk to the Tech Support person, tell me who I should > contact. Please, folks, don't make Jonathan kill again! [-: -- Alan Denney yosemiteATaccesscom.com Not a spokesman for Yosemite Botanical Munitions Inc. or Access Compost Supply.