Re: How "BETWEEN date1 AND date2" determines century

As I see it, the only place that really has the problem with the year 2000 is input from the outside world. That's in the form of queries by example with CONSTRUCT, INPUT fields, and data imported from other programs. One of the things we've done to prepare for century changes is to write a routine which will adjust a year to a timeframe relative to the current year. It accepts as inputs a year and a number of years prior to the current year that the year can be adjusted. It returns a 4-digit year. If the year is already 4 digits, it leaves it alone. If the year is 2 digits, it determines the current year and uses the year constraints also provided to come up with a 4-digit year. The year constraints are determined on a case-by-case basis. For example, given a 2-digit year for a birthday, you always want to go backwards (or equal to) the current year. So, you'd go up to 99 years back. (1896-1995) So, 98 for a year becomes 1898. For future projections, the year should always be greater than or equal to the current one, so your years back would be 0. (1995-2094) 80 would become 2080. We also use a default of 50 if none is specified so that a 2-digit year is placed -50/+49 years from the current one. (1945-2044) This works great when you're converting dates from files from some other system without the forethought to use 4-digit years. But, you'll still have problems with INPUT and CONSTRUCT. The problem with INPUT is that 4GL is going to automatically turn a 2-digit year into a 4-digit year in the 1900's for you. So, you always want to display all 4 digits of the year on your forms so that users can see immediately what their year got converted to. One could write a routine that adjusts a DATE variable and forces the year to be within a 100-year time range, relative to the current year, as described in the year-only function above. You could, without a lot of extra effort, add calls to existing programs in AFTER FIELD clauses to convert and redisplay the DATE. The only problem with this is if you get a date in the 1900's, you don't know whether the user typed 1/1/01 or 1/1/1901. By the time your AFTER FIELD clause sees the DATE value, it's 01/01/1901. If you force the date to be +/- 50 years from 1995, the user wont be able to type 01/01/1901, even if they want to, because you'll ALWAYS force it to 01/01/2001. (This might actually be what you want in a lot of cases. Maybe the user shouldn't be entering dates that long ago.) If one wants to get really tricky, they can use the get_fldbuf() function to see the user's original input. This could tell you whether they keyed 2 or 4 digits for a year. But you'd have to find a way to get it before 4GL sees you've tried to leave the field. ie. ON KEY statements. After you leave the field, get_fldbuf() will give you the same data that your DATE value contains. To demonstrate, in a date field, type "1/1/1" and before you've hit ENTER, have an ON KEY statement show you the get_fldbuf() value. It will show "1/1/1". Now, hit ENTER and have an AFTER FIELD statement show you the get_fldbuf() value. It's now "01/01/1901". Another possibility is to do your date INPUTs as CHAR, but that gets even messier because you'll have to do all those validations. (month <=12, etc.) As for CONSTRUCT, get_fldbuf() will return what the user typed as-is, like CHAR input. Maybe Informix could either provide some sort of 2-digit year to 4-digit conversion parameter in the form or maybe a function call in a BEFORE FIELD could set up how the conversion would work. Or, they could provide functionality to be able to view the raw input data for each field even in an AFTER FIELD clause. Then user programs could see what the user actually entered, not what 4GL converted it to. -- -Joel Schumacher jschumac@uns-dv1.jcpenney.com OR jschumac@jcpenney.com