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

ckipp@vth1.vth.colostate.edu (Cathy Kipp) writes: > 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. > This is all speculation, but I support the Informix decision to return an error code if the resulting date does not exist. That leaves it in MY hands to decide if I want a "previous" or "next" day result by testing for the error, adding (or subtracting) 1 day from the original date, and subtracting 6 months again. Seems like a logical solution to me... ---dave--- david@cssnet.sanford.nc.us [J. David Schronce] Computer Support Services - Public Access Usenet - 919/774-1485