Systems Modelling Ltd.
Y2K & PC Databases

Home ] Spreadsheet Best Practices ] Consulting ] Book List ] Euro Index ] Search ]

 

 

Related Pages:


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, Microsoft’s 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

 
 

  Traduisez / ‹bersetzen / Tradurre / Traduza / Traduzca Babelfish translator 

These pages are provided subject to Copyright and a disclaimer
Copyright © 2000-2004 Systems Modelling Ltd., Tara Hill, Gorey, Co. Wexford, Ireland.
Telephone +353-55-22294  Fax +353-55-22165 
Email Send email to sysmod.com  provided subject to the terms and conditions below.

PRIVACY: Your email address is treated as confidential and never disclosed without your explicit permission.
OPT-OUT: The phone and fax numbers and email addresses at sysmod.com provided on this web site are provided solely for one-to-one communications to Systems Modelling Ltd. We forbid any harvesting of email addresses from this site, or the inclusion of any sysmod.com address in any mail list without our explicit permission.
SPAM: Unsolicited bulk email to sysmod.com will be reported to SpamCop.

Last updated January 03, 2005