Year 2000 issues in PC Database packages
Access
A detailed list of Access issues is provided on the FMS Inc.
site: http://www.fmsinc.com/tpapers/year2000/index.html
Access 95 and later will interpret a two-digit year differently depending on the
version of OLEAUT32.DLL installed on your system. Some versions assume the century is the
current system date century; some window 1920-2019; Office 97 windows 1930-2029.
The default Access 2 input mask doesn't recognize any date past 1999. If you use a
short date format and input "00", the default goes to 1900. To correct this,
goto Design View, click on Input Mask, and type 99/99/9999 or whatever format you want.
That _is_ tedious to have to do for every control. You might wish to consult the Microsoft
KnowledgeBase PSS ID Number: Q132067 Article last modified on 05-27-1996 PSS database
name: ACCESS 2.00 WINDOWS. This article describes two methods that you can use on a field
formatted for the Short Date data type so that it displays a year later than 1999.
From the Microsoft web site:
Microsoft Products that Store or Manipulate Dates Year Limit
Microsoft Access 95 and before (full 4-digit "YYYY" year) 9999
Microsoft Access 95 and before (2-digit "YY" year shorthand) 1999
Microsoft Access 97 ("YY" year) 2029
Visual FoxPro ("YYYY" year) 9999
Q: What are the year limit details for Microsoft database products?
A: Every Microsoft database product including Microsoft Access, Visual FoxPro, and
Microsoft SQL Server stores years in 4-digit form. Microsoft provides users with the
ability to enter 2-digit short cuts for year data (i.e., "96" instead of
"1996"). When a user types in a 2-digit year, Microsofts programs actually
store the complete 4-digit year unlike many older mainframe programs, which store
just the 2-digit form. There is no standard way in the industry to interpret 2-digit year
short cuts and as a result, errors can occur when dates are entered and displayed only
using 2-digits. Of course, users of Microsoft products can always type in all 4 digits of
a year to clearly identify the date they want to store in order to reduce possible data
entry errors.
Microsoft Access 97 interprets "00" to "29" as short cuts for the
years "2000" to "2029." Microsoft Access 95, and earlier versions,
interpret "00" to "29" to be short cuts for "1900" to
"1929."
Xbase
"Xbase" is a generic term covering the dBase, Clipper, FoxPro, and
their Windows equivalents Visual dBase, Visual Objects, and Visual FoxPro, plus some older
products. All are based on the .DBF file format which has always (since 1983?)
stored date fields as YYYYMMDD, the full eight digits. Of course, screen entry and display
and printouts can still be in the two-digit year format, so the problem can still exist.
Clipper
Summer'87 and 5.x: Use SET CENTURY ON to display all 4 year digits.
The Oasis FTP site has a patch y2000s87.zip to simulate the SET EPOCH
feature in Summer'87 applications if you have the source code.
Clipper 5.x only: Can also use SET EPOCH TO 1950 for a fixed
window.
Or use SET EPOCH TO (YEAR(DATE())-70) for a sliding window.
Paul Lea Lujanac has an article on other issues in
Clipper applications. He points out that the LUPDATE() function wraps to zero because
there is only one byte in the DBF header to store the two-digit year.
FoxPro
Use SET CENTURY ON to display all 4 year digits.
Check out an article by Peter Somers in the February '96 issue of FoxPro Advisor for a
discussion of the topic. Also, articles by Brad Schulz in the October '96 issue and the
forthcoming December '96 and January '97 issues discuss ways to handle dates better. The
best documentation and software fix (MCDATE, a VALID clause for Date GETs) is in
FP2000A.ZIP, available in FOXFORUM on CompuServe.
Brad Schulz comments: The big problem is that the year 2000 is a leap year, and so
February 29th is a valid day in that year. However, if you have SET CENTURY OFF and
attempt to use MCDATE (above) it will be impossible to enter 02/29/00 because FoxPro
intercepts it as an invalid date before the VALID clause even fires. This is because
FoxPro interprets 02/29/00 as a year in 1900 and 1900 was *not* a leap year, so it is
rejected outright.
VFP 5.0 has the new command SET CENTURY TO cc ROLLOVER yy
Communication Horizons publishes Y2KFOX Millennium Bug Fix for FoxPro. Y2KFOX
sets century rollover in FoxPro and FoxBase+, so that 2-digit years default to the correct
century. It requires no reprogramming. They will be releasing Century Rollover for
CA-Clipper and Visual Basic 3 soon. The url is http://www.y2kfox.com
dBase
Use SET CENTURY ON to display all 4 year digits.
Other links
http://www.the-oasis.net Phil Barnett's Oasis
FTP site has more information and patches for Year 2000 in Clipper and xBase Languages.
Greg Holmes: Data File Headers and the Year 2000 www.ghservices.com/gregh/clipper/trix0011.htm
Scanning Source Code
Here some keywords relevant to Clipper programmers. Although xBase files store
dates as 8 digits, data input pictures and index keys could use /yy.
SET DATE ... Sets the global date display format, might be
"dd/mm/yy"
CTOD() Converts Char string in global format to Date
DTOC() Date to Char in global format
SET EPOCH TO 1940 Sets a window for dates 'dd/mm/39' is 2039
SET CENTURY OFF is the default, enables the display of
2 digit years.
STR(<expr>,2) Where <expr> _might_ be a year number
Look for String constants such as "yyyy", "19",
"@D", "@E"
There are other functions such as date(), cmonth(),cdow(), dow(), etc., but none of
them should affect the Y2K problem.
Ask for the evaluation version of
our own Clipper source code scanner. Feedback is welcomed, and the unrestricted
version can be purchased using a bank transfer.
Features
:
- scans executable files looking for suspicious or informative text strings
- scans data in dbf files looking for date-like data in non-date fields.
- scans *.dbf *.mem header structures optionally recursing subdirectories to obtain
Date fields
- scans *.prg *.ch *.frm *.lbl *.ntx *.ndx *.idx optionally into subdirectories looking
for references
- multiple "include" and "exclude" files of keywords
- prints cross-reference, detailed listing, file statistics, can recycle date variables
found into next run to further refine the search.
Other scanners
SCAN2000 at UKP50.00 from David Heafield at Speedwell Computing : http://speedc.com/
(offline May 2004)
MAXFIND available on Compuserve; by Stanley C. Peters, shareware, $21.incl p&p. Maximum 15 match patterns.
Another approach is to use a full text indexer like Wilbur (www.redtree.com). A search can span all files on all
drives.
And, of course, editors like Multi-Edit (www.amcyber.com)
allow scans of multiple source files.
All of these are dumb scanners - they don't skip comment lines or text embedded in
quotes, for example.
It's not just .PRG files
Check all the DBF,
NDX, NTX, MDX, IDX, CDX, MEM, FRM, LBL files. That will find things like:
- An index expression like:
SUBSTR(DTOC(datefield),7,2) +SUBSTR(DTOC(datefield),4,2)
+SUBSTR(DTOC(datefield),1,2)+otherfields
- Dates stored in character fields as YYMMDD.
- Take a numeric field representing a date as YYMMDD, multiply it by 100.0001, take the
last 6 digits and voila - MMDDYY !
- For your next trick: multiply the date by 10000.01 and take the middle six digits
for DDMMYY
And of course, you do have to have access to the source code. If your developer has
disappeared, you may have to use xBase decompilers to recover the source. But the results
are often unreadable because there are no comments and the variable names may be
artificial. Often, the only choice is to treat the application as a base for
designing a new system.
Powerbuilder
Ascension Labs offers a tool called PB Code Analyzer that was designed specifically for
PowerBuilder code.
See http://www.ascensionlabs.com/pbcodeanalyzer.htm
|