Re: (Q) Subtract 6 months from a date: (problem with date.)

Subject: Re: (Q) Subtract 6 months from a date: result=error -1267!!! In reply to Quentin North's posting I have been reading this thread and feel there is still another issue dealing with subtracting dates to come up with a full six months. stuff deleted... >What we want is the following results: > > (current date) (current-6 months) > 1994-03-15 1993-09-15 > 1994-03-31 1993-09-30 It would seem there is still a problem with this whole issue. If you want data that is six months old, you DO NOT want to include the same day in the previous month 1994-03-15 1993-09-15 In this example, isn't this 6 months and 1 day? Wouldn't you want 1994-03-15 1993-09-16 and 1994-03-31 1993-10-01 Unless of course in your statement you were to say the '>' than the current-6 months based on the original post. and to also talk about dberg's example of subtracting and adding units. > know to expect an imprecise result. This now raises the question: how do > you treat the computation of the example "April 30 + 1 UNITS MONTH"? Is > the answer May 30 or May 31? Do you apply the same or another special Wouldn't this be May 29? > operator to say: if the date on which the operation is performed is the I would agree you really need some VERY special operator. > last day of the month then the result should also be the last date of the > month? And how much performance are you willing to sacrifice to perform > all these determinations? I kind of like Alan Denneys idea, with a date-map table, though in pure isql it would seem to require more than just a join to satisfy Quinten's need. > "Violations" to going back six months on a month-granularity calculation > would be: > > Violation Desired Date-Back Desired Date-Forward > > March 31 September 30 October 1 > May 31 November 30 December 1 > August 29 (unless leap year) February 28 March 1 > My suggestion: set up a table with your desired date-map (need two versions > of the table, a does-wrap-leap-day version and a does-not-wrap-leap-day > version... or handle August manually). Join to the table using current > date as your key. > Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland By George, I think you've got it. :-) (or at least getting closer. I agree with the above, it is, at this time a site specific issue.) > 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. > Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland Rick Tucker rickt@wybbs.mi.org ...!uunet!4gen!indcon!rickt