Re: DATE and UNITS problem

alvinkoh@pts7.pts.mot.com (Alvin Koh) writes: >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? >Is this a known "bug" and what is the workaround? The other responders gave an accurate account of the issues at work here, so I won't repeat those. I wrote the following stored procedure to deal with the problem for a client, which I reproduce here in an abbreviated form. In this case, the business rule stated that if the current date plus some number of months results in an invalid date, then return the last valid date of the computed month. In this case the data type being returned is DATE; it could just as easily be a DATETIME. ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- CREATE PROCEDURE get_future_date (p_beg_date DATE, p_incr_mos INTEGER) RETURNING DATE; DEFINE p_future_date DATE; DEFINE p_adj_days SMALLINT; BEGIN LET p_adj_days = 0; WHILE 1 = 1 -- If the computed day is beyond the last day in the computed -- month, then subtract one day at a time until we find the -- last day of that month. ON EXCEPTION IN (-1267) LET p_adj_days = p_adj_days + 1; END EXCEPTION LET p_future_date = DATE (EXTEND (p_beg_date, YEAR TO DAY) - p_adj_days UNITS DAY + p_incr_mos UNITS MONTH); EXIT WHILE; END WHILE RETURN p_future_date; END END PROCEDURE;