Re: DBCENTURY and 4GL

On Wed, 12 Nov 1997, Jacob Salomon wrote: > I have a question regarding the much-hyped DBCENTURY environment > variable. I understand that the 7.2 engine uses it to help interpret a > 2-digit year. This is fine if I have a statement in dbaccess like: > > insert into orders(customer, order_date) > values(12345, "01/15/03")> > If DBCENTURY happens to be "C" and today's date is 11/12/1996, the above > date will be interpreted as 01/15/2003. (Assuming, of course, that I > have correctly interpreted the Guide to SQL - Reference). Yes, the engine will interpret that correctly without any particular help from DB-Access. If you wrote the same statement in I4GL and executed it, you would get the same effect because the engine is converting the string into a date, and not I4GL. > However, no application I know uses dbaccess directly. True, in general. > It is the 4GL app that gets the date in from a form. When the INPUT > statement is executed, the string "01/15/03" the user typed in the date > field is already stored in binary (days since 12/31/1899). Correct; here, the I4GL runtime code is translating the string into a DATE before the value is sent to the engine. > I am certain that the INPUT statement will have interpreted the > "01/15/03" as the year 1903, not 2003, since (as far as I know) 4GL knows > nothing about DBCENTURY. Well, it depends on which version you're using, but in general you are correct. All versions which are not DBCENTURY-aware will indeed convert the entered 2-digit string into a date in 1903, and never into 2003. As I understand it, version 4.20 I4GL is available and supports DBCENTURY. The corresponding 6.10 release is not yet available AFAIK. > Is this indeed the state of affairs with 4GL? This is highly relevant, > because we are getting close to the situation where people will be > entering due dates past 12/31/1999. I'll reiterate my advice; always, but always, display all 4 digits of the year. If the user is looking, they may notice that the date has been mistranslated. If only 2 digits are shown, the user doesn't know that the date has been misentered. Note that this applies even with DBCENTURY aware products; if DBCENTURY is set to F and the user enters 96, it will be entering 2096, not 1996, and unless you show all 4 digits of the year, the user won't know! And don't forget that you could probably put a check constraint on most DATE columns, such as: CREATE TABLE ... ( ... SomeDate DATE NOT NULL CHECK (SomeDate >= MDY(1, 1, 1970)) CONSTRAINT cN_datecheck, ... ) You can adjust the criteria appropriately, using some small set of control dates. You might also want to apply upper bounds, too, or instead. Eg: BirthDate DATE NOT NULL CHECK (BirthDate BETWEEN MDY(1, 1, 1880) AND MDY(12, 31, 2010)) CONSTRAINT cN_datecheck, These ranges could be computed, I think: SomeDate DATE NOT NULL -- Check that range is within 10 years (approx) of today CHECK (SomeDate BETWEEN TODAY - 10 * 365 AND TODAY + 10 * 365) CONSTRAINT cN_datecheck, However, be wary of this... If the user subsequently does an update of the column even without changing the value in the column (eg UPDATE Table SET * = (r_table.*) WHERE ... in I4GL), then the constraint may be violated because enough time has elapsed since the data was entered validly to place the date out of range, thus causing the update to be rejected. You could use an INSERT trigger instead, but the declarative constraint is generally clearer, and probably quicker (though I have no empirical evidence to justify that claim). Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>