Re: DATE and UNITS problem

In article <9502221924.AA00727@alvin.180.8.233.1> 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? Not a bug at all -- it's compliant with ANSI-specified behavior for datetime arithmetic using year-month intervals. You can't have this "work" (e.g. pick a value it "should be" based on some criteria of your own) and not break associativity of datetime arithmetic. This "feature" is an ANSI requirement; a good one, IMHO. (e.g. for SQL92, see the section on <datetime value expression> (6.14), General Rule 3b-c.) The alternative would not be logical for everybody; for example: a) what does "one month from January 31" mean? February 28th? February 29th in a leap year, and the 28th if not? March 1? March 2? b) what does "one month from January 30" mean? The same answer as in part (a)? If so, how can a month from today be the same day as one month from yesterday? (Consider: "It's just a jump to the left, and then a step to the right.") Remember, if you want a fixed number of days, you can add day-time intervals all you want and not have this problem -- it's when you need even months that the year-month vs. day-time interval arithmetic issues arise. In the real world, you will typically want a more "sensible" behavior, (depending on internal corporate rules), such as: If adding month(s) goes beyond the number of days in the target month, use the last day of the target month; If subtracting month(s) goes beyond the number of days in the target month, also use the last day of the target month. This is easy to do programmatically. Here's a sample of an "addmonth" and a "subtractmonth" algorithm (wrapped in a 4GL program that can run standalone) that will first try direct arithmetic; if that chokes (only happens 6 or 7 days out of the year), it will take the first day of the following month and then subtract 1 day so that you get the last day of the target month: ------------------------------------------------------------------------ main define dv, result datetime year to second define dd, firstofmo, lastofprior date whenever any error continue while (true) prompt "Enter date: " for dd if status != 0 then display "bogus date, Einstein!" display " " else let dv = dd -- convert to datetime -- ADDMONTH() let result = dv + 1 units month -- try adding a month. This will work -- all but 6 or 7 days of the year! if status != 0 -- if out of range, do the hard way then -- get the last day of next month let result = dv + 2 units month -- add 2 months (always works) let firstofmo = mdy(month(result), 1, year(result)) -- DATE of 1st day of second month from now let lastofprior = firstofmo - 1 let result = lastofprior -- convert to datetime end if display "datetime + 1 Pseudo-month = ", result -- SUBMONTH() let result = dv - 1 units month -- try subtracting a month. if status != 0 -- if out of range, do the hard way then -- get the last day of last month let firstofmo = mdy(month(dv), 1, year(dv)) -- DATE of 1st day of this month let lastofprior = firstofmo - 1 let result = lastofprior -- convert to datetime end if display "datetime - 1 Pseudo-month = ", result display " " end if end while end main ------------------------------------------------------------------------ It's not hard to alter the above to get the first day of the subsequent month, if that's the "rule" you need. -- Alan Denney aland@informix.com "We have a great holiday tradition in our family: we get together and psychologically abuse each other until one of us has a seizure. And then we have pie." -- Mark Roberts