The Oasis Clipper Source. Over 300,000,000,000 bytes served!

Year 2000 and xBase Languages

The .DBF date storage problem.

There is no Y2K date storage problem if you have selected a date type field for date storage. Date type fields store dates in DTOS() format, using 8 characters as YYYYMMDD. Four digit years have been stored correctly in .DBF files since dBase III.

Of course, if you have stored dates in either character or numeric fields, you may have a big problem. You will probably have to modify your code to work correctly after 01/01/2000.

DBFY2K17.ZIP (268K) is a tool for examining .DBF files for date usage. This highly configurable tool will make it simple to find date usage in existing .DBF tables, including numeric and character based storage.

DBF_Y2K.EXE is freeware, written in CA-Clipper 5.2e, and is provided with complete source code.

This utility quickly scans all .DBF files in a directory which match the filespec. DBF_Y2K creates a summary of date field year minimum and maximum, character date fields, numeric date fields, empty files and unused fields.

There are serious Clipper Tools III date problems!

Note from Phil: Computer Associates has released a patched version of Clipper Tools III that fixes these and the R6003 problems encountered in using Clipper Tools III. I strongly suggest you pick up their patch and use it. It appears to work correctly in my testing.

Clipper Tools III Update

If you use any of the Clipper Tools III date function; DOY(), ADDMONTH(), BoY(), EoY(), BoM(), EoM(), BoQ(), EoQ(), LastDayOM() or Quarter() (and I suspect any date function in CTIII now), you will need to examine how you use the function(s). According to the documentation, if you don't specifically pass a date as a parameter the current date is used by default. This doesn't work after December 31, 1999. The fix is simple. Replace all instances of DOY() with DOY(DATE()) (or or any of the above functions), and the problem disappears.

If you already are passing a date into any of these functions, no changes are required.

Use GSR as the search and replace engine to simplify the fix.

The .DBF header problem.

There is a very nice synopsis of this on Greg Holmes Data File Headers and the Year 2000 page.

Also, please see the link below for a way to patch Clipper.LIB to eliminate the Y2k problem in LUPDATE() in your Clipper software.

The xBase "last updated" date stored in the .DBF header has a problem in some languages and implementations. The header of the .DBF file stores the last update in bytes 1, 2 and 3 in hex.

0 filetype checkbyte
1 year( date() ) - 1900
2 month( date() )
3 day( date() )

The problem is that some software which reads and writes to .DBF files handles the year byte differently. There appears to be two types of different handling.

1. The software continues to increment the year byte beyond 1999. 2. The software does not continue to increment, but wraps to 00 instead.

...and there appears to be three different types of usability.

1. The software handles it (doesn't die)
2. The software doesn't handle it ok, but won't kill itself.
3. The software doesn't handle it ok, and kills itself.

The two programs known to have problems are:

dBase III+
Dbxl 1.3 ( note that Arago, which is the newer Dbxl is compliant)

dBase III+, by itself, wraps back to 00, and continues on. The problem comes when the year byte is incremented by some other software to be a value of greater than 99. dBase III+, in this case, says it's not a valid file and refuses to open the file.

Dbxl is really broken. It is able to increment and save the year byte beyond 99, but is then unable to open the file. Any file it updates becomes unopenable by itself after Jan 1, 2000.

How to fix the Problem?

DBXL Here is some very interesting information about Dbxl. This is a patch to the Dbxl executable that modifies Dbxl to work until 2027! Of course, if you modify Dbxl, you do so at your own risk, but if it's totally broken now, there's not much risk. You can access this information here. http://www.code-co.com/dbxl/dbxlfix.htm (this link appears to be gone forever)

Others You can manually patch the database file using Norton Disk Editor (DE) or some similar program. A freeware program called Y2KDBFIX is available via anonymous FTP from Cinderella at:

ftp://ftp.cinderella.co.za/pub (South African Site)
or ftp://parsifal.membrane.com/pub (USA Mirror Site)
(Note from Phil: These sites appear to be gone forever. A search on google.com revealed it in Hungary. I'd suggest getting it right away. Who knows if it will stay around.)
http://www.stadium.hu/szt/cl2000/
http://www.stadium.hu/szt/cl2000/y2kdbfix.zip

Here is a list of what we have discovered so far:

These programs rollover the year byte based on the most recent century (both the year 1900 and the year 2000 are represented as 00 hex):

Clipper
dBaseIII+
Fox 2.6

These continue to increment the year byte beyond 1999 (the year 2000 is represented as 64 hex, which converts to 100 decimal):

Xbase++
Visual dBase 5.5
dBXL 1.3
dBase IV 2.0
Quicksilver
dBase IV 1.5
CA-dBFast 2.0D-2
Arago (Dbxl) Lan 2.5
Microsoft Excel from Office 97
Microsoft Access 2.0

These can interpret the header year correctly with EPOCH or Set Century:

Clipper
Xbase++
Visual dBase 5.5
dBase IV 2.0
Fox 2.6
dBase IV 1.5
Arago (Dbxl) Lan 2.5

These cannot interpret a 2000+ header year and refuse to open the table.

dBaseIII+
dBXL 1.3

These cannot interpret the incremented byte, but can still use the file;

CA-dBFast 2.0D-2

According to consensus, dBXL is the only program that truly is broken in Y2K.

It increments the header year byte, but then cannot use its own file!

dB3+ and dBFast, while not properly handling the Y2K change, at least can continue to use their own files if no other system increments the header byte beyond 2000.

Misc:

Microsoft Excel

Excel can save files in multiple different .DBF formats. The DBF4 format is the default.

Here are the results from each format.

When saved in DBF2 format: 02 10 00 02 02 66 1C 02 (66 is 2002)
When saved in DBF3 format: 03 66 02 02 10 00 00 00 (66 is 2002)
When saved in DBF4 format: 03 66 02 02 10 00 00 00 (66 is 2002)

For a long time, I had some code here to kind of replace LUPDATE(). Since Tom Leylan has created his LUPDATE() patch, the ugly and barely usable code I had here was renedered useless. See the link just below for a real solution.