Re: How "BETWEEN date1 AND date2" determines century

}From: brent@hypercom.co.nz (Brent Jackson) }Date: Wed, 13 Sep 1995 16:55:54 +1300 }X-Informix-List-Id: <news.16943> } }johnl@informix.com (Jonathan Leffler) wrote: } }>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. } }However, in the year 2000 will the second date then refer to 2002 ? }If so, then surely the first date will refer to 2098 ? }If not, then will new versions developed after 1999 default to 20xx }instead of 19xx ? No! As I stated, the dates will refer to 1902 and 1998. If you don't believe me, set your computer's system clock to a date sometime in the next century and try it. I've done the experiment, as well as looked at the source code. It is simple. When the code sees a year of less than 100, it adds 1900 to the value. That is a hard-coded constant 1900. Unless there is a change in the code (and as I also stated there are no plans that I am aware of to change it), this won't change. }>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). } }Some of our historical systems store dates as char(6) fields (YYMMDD) }and this is based on ISO 8583 (1991). Date selects after the turn of }the century are going to be a little tricky. Correct. Don't use CHAR(6) to store dates that are to be manipulated as DATE values. }>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. } }My YYMMDD to Informix Date conversion routine assumes that if YY < 50 }then 100 years needs to be added to the date. All code that I've }explicitly written in the past 2 years have taken account of the end }of the century. (What I'm worried about is Informix date conversions }that use DBDATE). If you've coded for it, then you'll probably be OK. It is people who've blithely ignored the turn of the century who will be bitten badly. As far as I can tell, anything using DBDATE will continue to behave as it does at the moment; 1900 will be added to a 2-digit year. }>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. } }I make it about a 70% chance (since birthdays will always span two }centuries). It is more coincidental that three digit years will never }be an option! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>