Re: Possible Feb 29, 2000 Bug?

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". This is actually not a Y2K problem at all. It is also a FAQ, though whether it is in the official Informix FAQ is another matter. > Here's some sample code: > > CREATE TEMP TABLE tFoo > ( DateVal Datetime year to second > ) WITH NO LOG;> > INSERT INTO tFoo > ( DateVal ) > VALUES > ( "2000-02-29 00:00:00" );> > SELECT ( DateVal - INTERVAL (365) DAY(9) TO DAY ) > FROM tFoo; > > SELECT ( DateVal - INTERVAL (366) DAY(9) TO DAY ) > FROM tFoo; > > SELECT ( DateVal - INTERVAL (1) YEAR(9) TO YEAR ) > FROM tFoo; > > > The last select statement causes error -1267. Error -1267 > says: > > -1267 The result of a datetime computation is out of range. > > In this statement, a DATETIME computation has produced a value that > cannot be stored. This situation can occur, for example, if a very > large interval is added to a DATETIME. Review the expressions in the > statement, and see if you can change the sequence of operations to > avoid the overflow. > > I might have expected error -1206, but not the 1267 error. Is there > something I'm missing? 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. ************************************************************* ** Don't mix calculations with intervals in the YEAR/MONTH ** ** class with DATETIME values which include and components ** ** from the DAY..FRACTION class. ** ************************************************************* > The value of DBCENTURY has no effect whatsoever. Of course not; DBCENTURY only affects the conversion of 2-digit years in strings into DATE values. Period. There isn't a DATE value in sight, nor a string -- hence there is no way DBCENTURY could have an effect. > I found this problem running against 7.30.UC3 on Solaris 2.6. I've > confirmed the problem exists with 7.14.UD1 on Solaris 2.5 and 5.04.UC1 > on SunOs 4.1.4. > > I've opened a case with tech support, but I'm wondering if anyone else > has encountered this problem. Lots of people, lots of times. It is not a Y2K issue, although your example superficially appears like one (change 2000 in your examples to 1996, or 1904, or any other leap year, and you get the same results). The last calculation is not properly defined and produces an erroneous answer. Always has; probably always will. > Thanks for your time. 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. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.59 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn