Re: Informix Date Arithmetic - Subtracting Months

As a rule of thumb, you can't do certain forms of date arithmetic if the date values in question contain a month value. In other words, you could subtract your interval(6) month to month from a datetime year to month, but not year to day (which is essentially what the date values are). This makes absolutely no sense if you are thinking about the first of a month, i.e., 1-JUN less three months is 1-MAR. However, come up with a universal rule for subtracting one month from 28-FEB. Will it be 28-JAN? How about 31-JAN? Don't even start discussing this, BTW; this gets argued periodically in various formats and the answer is still the same: you can't. In fact, people have gone to war for less. There are various "fixes" to this out there. Check the archives and I'm sure you'll find something. HTH, David mzabk@my-dejanews.com wrote in message <6mrpnf$itd$1@nnrp1.dejanews.com>... > > >When I run the following query, I get an error on some records: > >select begin_date,end_date >end_date - begin_date,>rptend - INTERVAL(6) MONTH TO MONTH >from table_a > > >ERROR: 1267: The result of a datetime computation is out of range. > >I am trying to subtract 6 months from a date. It appears >that subtracting 6 months from a date like 8/31/1997 is causing >a problem because 02/31/1997 is not a valid date. > >I was wondering if someone had a work-around for this. > > >Thanks, > >Mike > >-----== Posted via Deja News, The Leader in Internet Discussion ==----- >http://www.dejanews.com/ Now offering spam-free web-based newsreading