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

In article <2obu40$90j@emory.mathcs.emory.edu> ckipp@vth1.vth.colostate.edu (Cathy Kipp) writes: >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. Oh, pish-tosh, as Peggy would say. This is ANSI-specified behavior. (e.g. for SQL92, see the section on <datetime value expression> (6.14), General Rule 3b-c.) >In your example, March 31 - 6 months, would only produce a non error >if the date September 31 existed. You could always just buy a cheap watch... then all months have 31 days. [-: >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. 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. 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. >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... Put another way, would you want 18 / 0 * 0 to give you 18, or an error? >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. >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. >Cathy Kipp ckipp@vth1.vth.colostate.edu Attached below is a sample for adding a "logical month" in 4GL which rolls over to the first of the next month (not what the original poster wanted, but structurally similar and easily modified). I suppose it's also doable in SPL... I still think a "mapping table" is the way to go, though. ============================================================================= Here's an algorithm (wrapped in a 4GL program that can run standalone) that will first try adding a month directly; if that chokes (7 days out of the year), it will get the first day of the second following month, then subtract 1 day so that you get the last day of the following 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 let dv = dd -- convert to datetime 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 Popiel-month = ", result display " " end while ============================================================================= -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland "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