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

Alan Denney responding to Cathy Kipp: > What *is* the appropriate response, then? Addition and subtraction are > by definition commutative and associative, e.g. > (A + B - B ) == A (A - B + B) == A etc. Of course, this only holds if B is a constant. UNIT MONTH is not a constant number of days. And in spite of that, the calculations work correctly approximately 29/30ths of the time. It seems the overloading of operators has been stretched too far in this case. > It seems that what you are asking for is that these no longer necessarily > hold true for dates and datetimes. Many other customers would go > completely ballistic over that. An appropriate error message is better > than an erroneous result any day. > > I suspect ANSI went through all these arguments when spec'ing this out. I'm sorry to hear that ANSI made the choice they apparently made after going through these arguments (I could make comments about design by committee, but we all know those jokes). > >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. > > Not our choice. You seem to want to hang this on some Informix Ivory > Towerism, but it's just not the case... No, instead it appears to be ANSI Ivory Towerism, which may be worse. > Put another way, would you want 18 / 0 * 0 to give you 18, or an error? Zero IS a constant, and division by zero is ALWAYS an error. Adding or subtracting UNITS MONTH is only an error under specifically defined conditions, depending on the size of the months in question. I see the reasoning behind this analogy, but it also seems faulty to me. > >I personally very much disagree with this, and would be happy if they > >would pick one - even if it weren't my preference, to avoid errors. I > > But you can "avoid the error" *yourself* and choose appropriate behavior > for your own installation with a tiny bit of advance planning. And I agree that code should check for errors and react accordingly. However, I disagree with the concept that (to use the analogy above) depending on which 18 you use, you might get an error (remember it is the datetime data value that makes the difference in behavior, "+ 6 UNITS MONTH" sometimes works and sometimes gives an error). > >believe subtracting months from the last day of the month should > >result in the last day of the corresponding month. > > > >But my beliefs aside, I have solved this - but in 4gl - by writing my > >own routine for adding a month. I do this using month, day, year, and > >the mdy functions. > > > >If anyone can convince Informix that a result is better than an error, > >perhaps this problem which anyone new to using date math in Informix > >stumbles across and becomes frustrated with could be solved. > > An appropriate error is always preferable to an incorrect result. I > am utterly convinced of this; perhaps others feel as you do. In most cases, I agree with you, Alan. In this case I agree with Cathy. The reasoning: the datetime UNITS MONTH is not a constant, therefore mathematical properties as described above cannot hold with operations on UNITS MONTH. If a MONTH unit were defined as always 30 days, I would expect everything to be as you (and apparently ANSI) describe/define it. However, that would be less than intuitive for most people (and the differences would be even worse for UNIT YEAR (12 UNITS MONTH or 360 days) -- wouldn't THAT throw people off.... [code segment deleted] In Alan's signature: > "You know 1994 is a leap year; there is a 29th of February this year. > People with digital watches are in heap big trouble, because there's > no way in Hell they'll know about it." > -- Rush Limbaugh 1/03/94 Another example of problems associated with trying to translate calendar processing into strict mathematical calculations. -- Bryan Klopfenstein bryan@carsinfo.com CARS Information Systems Corp. Cincinnati, OH Standard Disclaimers Apply