Re: 4GL DateTime Variables

Path: emory!swrinde!zaphod.mps.ohio-state.edu!cis.ohio-state.edu!rutgers!pyrnj!pyramid!infmx!johnl From: johnl@informix.com (Jonathan Leffler) Newsgroups: comp.databases.informix Message-ID: <1991Nov14.182028.11657@informix.com> Date: 14 Nov 91 18:20:28 GMT References: <20490001@hpspkla.spk.hp.com> Sender: Jonathan Leffler (johnl@informix.com) Organization: Informix Software, Inc. In article <20490001@hpspkla.spk.hp.com> engstrom@hpspkla.spk.hp.com (Kathleen Engstrom) writes: >Hi > >I'm fairly new to the world of I4GL programming. I'm trying to format >some datetime variables. I was able to use expand to break it up and EXTEND? >get just the date portion of the variable, but I haven't found anything >that will allow me to format the result. (This is in a report) There are basically no formatting routines for DATETIME. You can convert a DATETIME YEAR TO DAY into a DATE by DATE(dtime), and then format as with any other date -- that's easiest. You can assign a DATETIME to a string, but the string is right-justified (I know not why -- it just is!). You can then disembowel the string in any way which takes your fancy. Incidentally, if you find you need to order by a datetime variable inside a report (ORDER {INTERNAL} BY), your C4GL compiler may have a fit; use a character string instead of a DATETIME and you'll be OK, even if you pass a DATETIME into the report. > >I would like to put the date in a more human readable form. (i.e. 11/7/91 >instead of 91-11-7). > >My second question: I would like to calculate the difference in two dates >and then subtract out weekends and holidays from the result. I would >appreciate any pointers, suggestions on how to do that cleanly. A turnkey >application would be even better :-) Non-trivial, to put it mildly. With random holidays (such as Easter, Christmas) as well as weekends, the technique I use is: DEFINE d, date_1, date_2 DATE DEFINE dn, i INTEGER CREATE TEMP TABLE T1 (counter SERIAL, wanted DATE) WITH NO LOG;-- LET date_1 be First date in range to be analysed -- LET date_2 be Last date in range to be analysed LET dn = date_2 - date_1 + 1 -- Number of days in range FOR i = 0 TO dn LET d = date_1 + i IF DAY(d) >= 1 AND DAY(d) <= 5 THEN -- Eliminate other days as required INSERT INTO T1 VALUES(0, d) END IF END FOR CREATE UNIQUE INDEX K1_t1 ON T1(counter) CREATE UNIQUE INDEX K2_t1 ON T1(wanted) Now to find the number of days between your two dates, look up the value of counter corresponding to each date and take the difference. Of course, there's nothing to stop you doing this with a permanent table -- indeed, in the system where I used this technique, the table was permanent because it excluded both English and American bank holidays, and neither of those form a regular pattern. >Thanks in advance > >Kathleen Hope it helps. -- ------------------------------------------------------------------- Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>