Re: Year 2000 and 2 digit years fields

At the risk of boring you by resuscitating an old subject... >Date: Sun, 10 Mar 1996 10:03:30 -0500 (EST) >From: Nick Nobbe <nnob@loc.gov> >X-Informix-List-Id: <list.8975> > >On 8 Mar 1996, Pat O'Grady wrote: >> Currently when a 2 digit year is entered on a form for a date field, >> the century defaults to 1900. I assume in the year 2000, this will >> change and the century will now be 2000. Am I correct? > > No: 00 = 1900 currently if your DBDATE environment variable is set > to 2 digit years. So you should change it and all your software > designed to think otherwise. That may be difficult but it's the > best solution. > >> Does any one know if there is a database type environment variable >> that will allow you to specify which 2 digit year values belong to >> which century? We are looking at whether we will have to change all >> our 2 digit entry year fields on date fields to 4 digits to handle >> the century change. Changing to 4 digit year fields will take a >> lot of work and add keystrokes to users. > > At our recent annual user group forum, I learned that Informix has > just this kind of feature, namely DBCENTURY, in version 7.2. > You can set this variable to closest year, i.e. year in the closest > century (where 1/1/02 = 1/1/2002), closest past year, > closest future year, and closest future year, with your date > (DBDATE) environment variable set to 2-digit years. Nick is correct. If you are using a product based on any version of ESQL/C prior to version 7.20, the current behaviour, which is to add 1900 to 2-digit years when a string is converted to a date, will continue to occur. This means, if you have not already cottoned onto it, that in the year 2000, if your forms do not allow the user to enter and see 4 digits for the year, you will inevitably get incorrect data in the database. You can help prevent that by adding CHECK constraints to your data columns, such as: CHECK (YEAR(datecol) >= 1950) Only if your code is based on a product using the (as yet unreleased) 7.20 ESQL/C, or some later version, will you get the more flexible behaviour given by the DBCENTURY environment variable, which is documented below. At the moment, there are no plans to back-port this to any of the version 4, 5 or 6 products. This is the slide which I used at the Washington Area Informix User Group meeting. =========================================================================== DBCENTURY -- 2-digit years and 2000 The environment variable DBCENTURY should be set to one of: C closest (generally most useful) P past (closest previous year) F future (closest future year) R present (present century) Given today's date of 2/16/96 (American format :-) | Date entered DBCENTURY | 1/1/90 | 1/1/02 | 1/1/97 | 2/16/96 ------------------------------------------------------------------------ C | 1/1/1990 | 1/1/2002 | 1/1/1997 | 2/16/1996 ------------------------------------------------------------------------ P | 1/1/1990 | 1/1/1902 | 1/1/1897 | 2/16/1896 ------------------------------------------------------------------------ F | 1/1/2090 | 1/1/2002 | 1/1/1997 | 2/16/2096 ------------------------------------------------------------------------ R | 1/1/1990 | 1/1/1902 | 1/1/1997 | 2/16/1996 With thanks to June Tong at Informix for this information =========================================================================== Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>