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

>From: nsqjn@ifl.co.uk (Quentin North) >Subject: (Q) Subtract 6 months from a date: result=error -1267!!! >Date: 5 Apr 1994 14:49:30 GMT >X-Informix-List-Id: <news.6168> > >In a Online 5 query we are subtracting 6 months from a date stored in a >table. However, when this date is 31 March 94 subtracting 6 months yields >an SQL error -1267. > >In DB2 we have no problem with this and subtracting 6 months from 31 March >94 yields 30 September 93. Is this a bug in Informix? Quentin, 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. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>