Re: Date Arithmetic with Non Date Fields

>From: keithm@hpcvsnz.cv.hp.com () >Subject: Date Arithmetic with Non Date Fields >Date: Fri, 4 Mar 1994 17:48:45 GMT >X-Informix-List-Id: <news.5746> > > We have some software that uses an Informix database. The problem is > that date and time fields are integer fields that are stored as yymmdd > and hhmmss respectively. I'd like to do date arithmetic to determine > intervals between dates. I'd prefer to use Ace although at this point > it doesn't look possible or easy. > > Has anyone run into this and possibly have a routine to take care of > date arithmetic on non-date fields. Why not use proper DATE columns? If you set DBDATE=y2md0, you can get the default output format to look the same as your current storage format. Also, what are you going to do in 5 years time when people start entering dates for the next year? If you must use you integer form and ACE, then I would do: DEFINE VARIABLE cnumb CHAR(13) VARIABLE cdat1 DATE VARIABLE cdat2 DATE VARIABLE intvl INTEGER ... ON EVERY ROW -- or any other control block LET cnumb = intdate1 LET cdat1 = DATE(cnum) LET cnumb = intdate2 LET cdat2 = DATE(cnum) LET intvl = intdate2 - intdate1 ... Beware: do not try eliminating cnumb. If you do, ACE (and I4GL) will interpret 940307 as 940,307 days since 31st Dec 1899, which is 4474-06-21. I haven't actually run the code above through ACE, so there may be typos. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>