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

In article <2obb4q$ml@brsuni.ifl.co.uk> nsqjn@ifl.co.uk (Quentin North) writes: >... >What we have is a date column that contains a complete year to day ^^^^ do you mean *date*, or *datetime*? I would NOT recommend mixing datetime primitives with date columns/variables beyond what is documented to be appropriate... >date. This column needs to specify dates including the day in month. >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. > ^^^^^^^^^^ > >What we want is the following results: > > (current date) (current-6 months) > 1994-03-15 1993-09-15 > 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? You need to really spell out what behavior you want, e.g. look at each "violation" of the direct-arithmetic rule and determine behavior. "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 August 30 February 29|28 March 1|2 August 31 February 29|28 March 1|2|3 October 31 April 30 May 1 December 31 June 30 July 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. >Quentin North -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland "Will Internet be to the Information Age what ham radio was to the Cold War? If a phone tree falls in the forest and nobody's on-line, will it ring?" -- Ian Shoales