Re: (Q) Subtract 6 months from a date: result=error -1267!!!

ckipp@vth1.vth.colostate.edu (Cathy Kipp) writes: >Quentin North writes: >-> ... >->select * from tab >->where datecol between date(current) - 6 UNITS month and date(current) >-> >->This should produce all rows within 6 months to the day. >-> ^^^^^^^^^^ >-> ... >-> (current date) (current-6 months) >-> 1994-03-31 1993-09-30 >-> >The major problem here is that the great gods of Informix decided that >when you are subtracting (or adding) months from a date, if the >corresponding day of the month does not exist in the result month, >an error is returned. ... >When I found out about this problem after we got Informix, I called tech >support and complained. An error is not an appropriate response. Given >this reponse you can never depend on Informix date math when adding or >subtracting months. >Informix's argument is that since no corresponding date exists, it is >unclear as to whether the answer to your date subtraction should be >September 30 or October 1. Because it is unclear, they prefer to return >an error rather than make a choice. I won't take a stand, Cathy, one way or the other whether an error or an erroneous value should be returned in this circumstance. Consider, though, that to be mathematically correct if you subtract "5 UNITS 10" from the decimal number 65 and then add "5 UNITS 10" back to the answer, the result had better be your original value. The same principal applies here. If you were allowed to subtract "1 UNITS MONTH" from March 31 without an error and then added "1 UNITS MONTH" back to the answer you'd get March 28 (or 29). I have a problem with this. I got to thinking, though, as I read through this nth thread of this troublesome subject, that in the case of integer arithmetic involving computations with fractional intermediate results, we have the ROUND operator with which we may assert that we want our answer to be to the "nearest integer value", even though it isn't the precise fractional result that is computed. What if a similar operator were available in date arithmetic whereby the user asserts that computations which result in a non-existent date are "rounded" to the nearest end-of-the-month? Now if you add "1 UNITS MONTH" back to your answer, by your own assertion you know to expect an imprecise result. This now raises the question: how do you treat the computation of the example "April 30 + 1 UNITS MONTH"? Is the answer May 30 or May 31? Do you apply the same or another special operator to say: if the date on which the operation is performed is the last day of the month then the result should also be the last date of the month? And how much performance are you willing to sacrifice to perform all these determinations? ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own.