Re: SE RDS v4.10: Adding units of MONTH to a DATETIME

This is a FAQ, though I don't think it is actually covered in the FAQ. Kerry -- please note! >From: "tech@compsys.demon.co.uk" <158.152.33.120@rmy.emory.edu> >Date: Wed, 18 Oct 1995 08:59:51 GMT >X-Informix-List-Id: <news.18019> > >I wondered if anybody has come across this one before: Oh yes; lots of people have noticed this. >I am using Informix SE RDS v4.10 and want to use a datetime variable >to determine a date 6 months forward of the current system date. > >I tried using something like: > >let new_date = today + 6 units month > >Unfortunately, for a date such as 31/10/1995, the above statement sets >new_date = 31/04/1995 and the program, obviously, reports an error. I trust it generates 31/04/1996 :-) If you have read the tutorials and so on about DATETIME and INTERVAL, you will realize that there are two classes of INTERVAL; those which involve YEAR and/or MONTH (let's call them Interval-YM), and those which don't (Interval-DF). You cannot combine intervals of the two different classes in the same calculation. This is enforced by the libraries which support datetime and interval computations. To calculate the value: LET new_date = today + 6 units month The library actually has to compute: LET new_date = DATE(EXTEND(today, YEAR TO DAY) + INTERVAL(6) MONTH TO MONTH) I think the EXTEND is the operator for converting a DATE to a DATETIME; it doesn't much matter if it doesn't, but there is a conversion of the DATE to DATETIME YEAR TO DAY. Now, although the documentation doesn't say it, and the library doesn't enforce it, you are in effect mixing an Interval-YM value with a computation which involves DAY too, and the effects are not wholly determinate. Specifically: what do you mean when you add 6 MONTHS to Halloween? Some people might want 30th April; others might want 180, 182, 183 days added; what happens with leap years; etc. What happens if you add 1 UNITS YEAR to a leap day (29th February). Basically, as Clem Akins pointed out, you are going to get anomalies if you try mixing DATE computations with Interval-YM units. As another example, let's suppose that the system was 'fixed' so that adding 6 UNITS MONTHS rounded to the end of the month. LET old_date = DATE("31 Oct 1995") LET new_date = old_date + 6 UNITS MONTH LET xxx_date = new_date - 6 UNITS MONTH Now, under any normal arithmetic, old_date and xxx_date should be the same value, but they wouldn't be; xxx_date = DATE("30 Oct 1995") So, the basic problem is that there is no good answer to the computation requested, and the library should reject it as indeterminable, but doesn't. >Has this problem been fixed in any later releases of RDS ? No. >Suggestions greatly appreciated. Don't do it. Use + 18x UNITS DAY. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>