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

Quentin North writes: -> ... ->However, we wish to do a select on this column producing the last 6 ->months rows to a specific day. Hence our select looks like: -> ->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 -> ->Similarly this problem occurs on a leap year (1994-02-29) if you ->subtract anything other than four years using (- units years). -> ->How can we achieve what we want with informix? Quentin, 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. In your example, March 31 - 6 months, would only produce a non error if the date September 31 existed. 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 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 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. Hope this helps a little! - Cathy -------------------------------------------------------------------------------- Cathy Kipp ckipp@vth1.vth.colostate.edu Veterinary Teaching Hospital Phone: (303) 491-1294 Colorado State University Fax: (303) 491-1205 -------------------------------------------------------------------------------- Disclaimer:These are my opinions and may not represent anything at all relevant.