RE: Informix Date Arithmetic - Subtracting Months

Just an idea... I had a 4GL application that had to run on the last day of every month, and calculate back 6 months. Basically, this is how I found the "6 months ago" date: # You don't have to use "today" here; you could start with any date LET date1 = mdy(month(today), 1, year(today)) # Go back one month less than your goal LET date2 = date1 - 5 units month # Going back one more day puts you at the last day of the month, 6 mos. ago LET six_mo_date = date2 - 1 units day Of course, you probably COULD put this all together into on big LET stmt. Regardless, maybe this will give you some ideas... HTH ================================ Paul A. Mosser, Open Systems DBA Wells Fargo & Co. Tempe, Arizona mosserp@wellsfargo.com ================================ > -----Original Message----- > From: David Coburn [mailto:dcoburn@worldvision.org] > Sent: Thursday, June 25, 1998 11:07 AM > To: informix-list@iiug.org > Subject: 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 > > >