Re: DATE and UNITS problem

Disclaimer: this is my view of the problem, and is probably not Informix's view of it; my view is not, in any sense, an official statement of Informix's view. >From: alvinkoh@pts7.pts.mot.com (Alvin Koh) >Date: Wed, 22 Feb 1995 11:24:00 GMT >X-Informix-List-Id: <news.11716> > >I have a 4gl program that uses the DATE and UNITS as in > > main > define d1 date > define d2 date > let d1 = "1/31/1995" > let d2 = d1 + 1 units month > display d2 > end main > >The program fails because it trys to assign d2 a value of "2/31/1995" which >is of course an invalid date and results in the following error: > > FORMS statement error number -1267. > The result of a datetime computation is out of range. > >I am using ONLINE 5.01.UD2 and 4GL RDS 4.11.UC2. I have also got similar >results on ONLINE 5.03.UC1 and 4GL RDS 4.12.UE1. Has anyone encountered this? It reproduces in 4.13.UD1 I4GL-RDS as well, and 6.00.UE1, and 6.01.UD1, and ... It can also be reproduced by either OnLine or SE by placing the calculation into a SELECT statement: SELECT DATE("31/01/1995") + 1 UNITS MONTH FROM Systables WHERE Tabid = 1; This applies to any version not earlier than 4.00, which is when the DATETIME and INTERVAL types were introduced. >Is this a known "bug" and what is the workaround? Yes, providing the "bug" is accepted as being in the 4GL code above or its derivatives, and not in the product proper. Why? Remember that there are two classes of interval, those spanning YEAR and MONTH, and those spanning DAY, HOUR, MINUTE, SECOND and FRACTION. The addition code above requires the DATE value in d1 to be converted to a DATETIME, and the qualifier will be YEAR TO DAY. The "1 UNITS MONTH" clause is an INTERVAL expression which could have been written INTERVAL(1) MONTH TO MONTH. Now, when an interval involving months is added to a datetime involving days, the result is indeterminate. How many days are there in a month? 28, 29, 30, 31? So what value should be added? There is no way for the system to know. And it is not much help to say "well, obviously, it should produce the last day of the month one month later when the date is the 31 Jan. What about when it the current date is 29 Jan. Also, note that if the computation of 31 Jan plus one month produced 28 Feb in a non-leap year, subtracting one month from the result would presumably leave you with 28 Jan. (If it didn't, what would it leave you with?) So, under any system for handling adding or subtracting intervals which involves juggling with the last day of the month, the system loses closure on computations. An error is, to my way of thinking, the best result, as the computation is indeterminate. Whether the error given is the best possible is another matter. It is irritating (to me) to have to admit, therefore, that the 4.1, 5.0, 6.0 and 7.1 Informix Guide to SQL Reference manuals all cite the example below (see pages 3-22, 3-22, 3-25, 3-33): DATETIME(1991-08-01) YEAR TO DAY + INTERVAL(3-5) YEAR TO MONTH Result: DATETIME(1995-01-01) YEAR TO DAY Change the base DATETIME to DATETIME(1991-09-30) and the computation will fail with the -1267 error. That makes it, in my view, a poor example. I also think the computation should fail in all cases, not just at the end of a month. This view, obviously, does not match what appears in the documentation -- hence my disclaimer at the top of this message, and the repeat down here at the bottom: what I say in this case must not be represented under any circumstances as official Informix policy on the subject. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>