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

In article <2nuqj0$50q@emory.mathcs.emory.edu>, johnl@informix.com (Jonathan Leffler) says: >The problem is reproducible with: > >CREATE TABLE x (d2 DATETIME YEAR TO DAY); >INSERT INTO x VALUES ("31/3/94", DATETIME(1994-3-31) YEAR TO DAY);>SELECT (d2 - 6 UNITS MONTH) d2 FROM x; > >This produces error -1267. > >SELECT (d2 - 182 UNITS DAY) d2 FROM x; > >This produces 1993-09-30 quite happily. > >The problem, which is not well diagnosed by error -1267, is that you are trying to >subtract an INTERVAL MONTH TO MONTH (that's what 6 UNITS MONTH means) from >a DATETIME which crosses the YEAR & MONTH vs DAY-FRACTION divide. Because >there is no deterministic way to encode these values, Informix disallows >such mixed operations. > >If you use a DATETIME YEAR TO MONTH and subtract 6 UNIT MONTHS from March >1994, it produces the correct answer (1993-09). > >Note that in DB2, if you subtract 6 months from 31 March 1994, and then add >6 months back onto it, you get 30 March 1994, which is equally unintuitive. >By contrast, those calculations which Informix permits are reversible; >doing ((col + x) - x) leaves you with the original value in col. Trouble is this does not really do what we want to do. What we have is a date column that contains a complete year to day 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? Quentin North