Re: How "BETWEEN date1 AND date2" determines century

>From: john@miso.wwa.com (pinoy_ako) >Date: 9 Sep 1995 23:37:04 -0500 >X-Informix-List-Id: <news.16850> > > As we all know, year 2000 is just 5 years away. So what > will happen to current data entry screens which only > accept dates formatted as "mm/dd/yy"? Right now, the > 4gl(?) adds "19" before the yy. If you enter "123195", > the final format will be "12/31/95" > > Also, how will the database handle the query below if the > query is run in a day after year 2000? > > SELECT .... WHERE col1 BETWEEN "06/12/98" AND "06/12/02" > > Is the database intelligent(?) enough to know that 98 above > is 1998 and 02 is 2002? Or do I have to make it a full year > condition? (BETWEEN "06/12/1998" AND "06/12/2002") >From: Spokey the Wheeler <spokey@haunted.demon.co.uk> >Date: Sun, 10 Sep 95 14:40:55 GMT >X-Informix-List-Id: <news.16855> > >In article <42tq1g$45v@news.wwa.com> john@miso.wwa.com "pinoy_ako" writes: > >> As we all know, year 2000 is just 5 years away. So what >Is it really? Time flies when you're having fun! :-) >> Is the database intelligent(?) enough to know that 98 above >> is 1998 and 02 is 2002? Or do I have to make it a full year >You've got to be kidding! >> condition? (BETWEEN "06/12/1998" AND "06/12/2002") >IMHO, I think you'ld be crazy to trust any vendor to handle the dates for you. >I suspect that software released after 1/1/2000 will default a "20", and >there's no way they'd handle the 98-02 issue. As 'Spokey' implies, you have to enter the date in full. But do you do no long range planning in your database? Anybody who does projections more than 5 years ahead has long since run into this problem. Anybody in the mortgage business has known about this sort of issue since 1970 or so. And if you are dealing with birth dates, '98' could still mean 1898, though the number of people to whom that applies is not large. At the moment, the definition of the two dates is unequivocal. The first is (6th December 1998 or 12th June 1998), depending on your DBDATE and (possibly) locale environment variables, and the second is referring to the 1902. Since the first date in the range is greater than the second, the data selected is the the empty set. There are no plans that I have heard of to change the meaning of the 2-digit date -- so, as far as I can tell, if you are concerned about entering dates accurately, ensure that you display all 4 digits of the year. Note that you can enter a 2-digit code and it will be converted to 19xy, and if it is displayed correctly, there is no problem, and if it is incorrect (should be 20xy and shows as 19xy, you can fix it). I agree that from a usability standpoint, it would be convenient if there was a way of specifying that double-digit years 70..99 are to be treated as 1970..1999, and years 00..69 are to be treated as 2000..2069. In the past, I have asked for ideas about how to deal with it (January 1994 was the last time), and the consensus reached from perhaps half-a-dozen replies was that an environment variable which dictated the breakpoint is a good way of dealing with it. There are no plans that I am aware of to implement such functionality. All customers with whom I've had dealings since 1990 at least have been volubly encouraged to view the change of century with caution, and therefore to display all dates with 4-digit years; I've advocated as much on c.d.i on more than one occasion previously, and this is my bi-annual renewal of a plea to make sure that anything you design now takes the change of century into account. In the meantime, you can join the ranks of the enlightened, and make sure that all dates are displayed with 4-digits, and thus get some sleep between 1st January 2000 and 31st December 2001 when everyone who hasn't prepared for this will be running round headless trying to fix all the programs designed in the last five or more years that didn't take into account the turn of the century. Jan 2000 strikes me as being an excellent time to take a sabbatical! It is, in my view, a historical curiosity that computers began to prevalent at a period in a century when 2-digit years were more or less unambiguous. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>