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

David Berg (dberg@informix.com) writes: } 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. } ... } >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. The real problem is that the unit "MONTH" is not a constant. Therefore, there really is no mathematically correct, "undo-able" answer for all cases. It sounds like the only case that is an issue is when the original date is xx/31/xx (mm/dd/yy format) and the "destination" month does not have that many days (actually it is a little worse if the destination is Feb...). I am afraid that I have to agree that returning an error in approximately 1/30th of all cases containing valid data seems a bit ridiculous to me. I would rather see a method chosen with a disclaimer that the end of the month can create inaccuracies. Regarding David's further discussion: } 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? Considering the work that has to go into verifying which size MONTH is needed for each UNIT MONTH.... In this case, I guess we have the assertion to round to the nearest end-of-month, so to me the answer should be May 31. Likewise if, in a non-leap year, the date were "February 28 + 3 UNITS MONTH" and your rounding assertion were in place, it should be May 31. I would tend to lean toward a simple choice -- always round or never round. But the rounding only takes effect when the last day of the month is involved and there is more than one possible result (like the May 30-31 situation above). Regardless of that decision, the conditions currently generating errors should give the last day of that "destination" month and not error messages. Naturally this is all my opinion (and only mine). -- Bryan Klopfenstein bryan@carsinfo.com CARS Information Systems Corp. Cincinnati, OH Standard Disclaimers Apply