BDE and the Year 2000

By: Quinn Wildman

Abstract: BDE and the Year 2000

  • Product: BDE
  • Version: All
  • Platform: Windows 95, Windows NT, Windows 3.1
  • This document describes how the Borland Database Engine (BDE) operates in conjunction with the year 2000. It discusses all native drivers, SQL Links and ODBC.

    What is Year 2000 compliance? Simply put, it is the ability to store and retrieve dates in any 4 digit century (the year 10000 will be a real problem!) and handle leap year days correctly. Given this minimum specification, the BDE clearly meets it. At this time, no further specification has been agreed upon by any standards committee. (As you read this document, youll see that various vendors handle 2 digits years differently.) However, the March 1998 issue of Computer Magazine has proposed the following standard:

    Technology, including but not limited to, information technology, embedded systems, or any other electro-mechanical or processor based system, when used in accordance with its associated documentation, is capable of accurately processing, providing, providing, and/or receiving date data from, into, and between the twentieth and twenty-first centuries, and the years 1999 and 2000, including leap year calculations; provided that all other technology properly exchanges date data with it.

    All drivers handle leap year days correctly. Note that the year 2000 has a leap year day. This document does not discuss leap year days further because all drivers handle them correctly.

    The one way you can insure that your programs that use the BDE are Year 2000 compliant is to always use 4 digit years. Given this one proviso, you can ignore the rest of this document. The rest of this document considers what happens when you enter 2 digit years for each driver.

    With the exception of Paradox , dBASE, and FoxPro tables and when using QBE, the BDE passes dates as entered to the Client Software. No century is assumed. It is the responsibility of the connecting client to interpret the century. Below is how each driver type is known to respond to 2 digit years. Most information has been pulled from the associated vendors web site.

    QBE
    If YEARBIASED in BDE Administrator is TRUE, all 2-digit date queries against all databases have 1900 added to them. If YEARBIASED in BDE Administrator is FALSE, all 2-digit date queries are assumed to be in the first century (Year 0001 to 0099). Because of this, all dates not in the 20th or 1st century must be expressed as 4-digit years.

    Paradox, dBASE, FoxPro, Local SQL
    Dates entered into tables assume the current century of the local CPU.

    For BDE versions less that 5.0, Local SQL against Paradox, dBASE and FoxPro tables assume the current century to be 1900 . For Local SQL to use a century other than 1900 using BDE prior to 5.0 a 4-digit year must be used.

    For BDE version 5.0 and later, local SQL against Paradox, dBASE and FoxPro tables assumes dates with 2 digit years to be in 1900 for years >=50 and years to be in 2000 for years <50. For Local SQL to use a century other than these defaults with BDE 5.0, a 4 digit year must be used.

    Access 95
    Microsoft

    Parsing on date entry. OLEAUT32. 2.20.4049 or greater is required in order for Access 95 to correctly recognize 2-digit date shortcuts as falling within a window between 1930 and 2029. Without that file update, Access assumes that all shortcut dates fall in the current century. See Microsoft for additional information.

    Access 97

    2-digit shortcut handling:

    Microsoft Access 97 interprets dates entered with a 2-digit shortcut to mean the 21st century in the following way.

    1/1/00 through 12/31/29 are interpreted as 1/1/2000 through 12/31/2029

    1/1/30 through 12/31/99 are interpreted as 1/1/1930 through 12/31/1999 See also Microsoft

    Interbase
    From InterBase

    InterBase provides a reasonable default interpretation of two-digit years. The InterBase server interprets a two-digit year as the nearest year within a 50-year range. For example, 2/28/48 is interpreted as 1948, whereas 2/28/47 is 2047.

    Oracle
    From Oracle

    If the current year is in the second half of the century (50 - 99)

    • and a two-digit year between 00 and 49 is entered: This will be stored as a next century year. E.g. 02 entered in 1996 will be stored as 2002.
    • and a two-digit year between 50 and 99 is entered: This will be stored as a current century year. E.g. 97 entered in 1996 will be stored as 1997.
    If the current year is in the first half of the century (00 - 49) and a two-digit year between 00 and 49 is entered:
    • and a two-digit year between 00 and 49 is entered: This will be stored as a current century year. E.g. 02 entered in 2001 will be stored as 2002.
    • and a two-digit year between 50 and 99 is entered: This will be stored as a previous century year. E.g. 97 entered in 2001 will be stored as 1997.

    Sybase
    From Sybase

    Implied century rule: <50 = 2000, >49 = 1900

    Compliance issues description or comment relating to current product version:

    Informix
    From Informix

    Customers should be aware that Informix products often depend on data from other parts of the system, and Informix year 2000 compliance is not effective unless all of the customer's hardware, operating system, other software, and firmware correctly interpret and/or translate date data into a four digit year date and, when used in combination with the Informix products, properly exchange date data with it. It is the customer's responsibility to understand the manner in which applications are written to accept a date value and the habits of users entering dates into these applications. Generally, year 2000 issues associated with these problems must be addressed at the application level.

    As described below, Informix has added a "DBCENTURY environment variable" feature to the Informix products listed in the table below which may help users provide year 2000 compliance in their existing applications without re-coding. Customers are encouraged to test their applications for the compliance described above. Customer applications, built using Informix products, which use only two-digit date storage may benefit from the DBCENTURY feature described below.

    DBCENTURY Feature to Aid in the Year 2000 Solution

    The DBCENTURY environment variable was implemented to provide customers with an API-level year 2000 solution eliminating the requirement of the customer to modify application source code. The DBCENTURY environment variable is an indicator which controls how Informix products set the default century value for Date and Datetime datatypes when the user has not entered the full four digits for a year through a custom developed application. No changes are required to the application code whatsoever to take advantage of this feature. The only action to be taken by the application user is that DBCENTURY must be added to the login profile of each user on the client system, and is thereafter automatically applied to all Informix applications associated with that login id.

    For those versions of Informix products for which the DBCENTURY environment variable is supported (see table 2 below), if the DBCENTURY variable is not set in the user's profile, the Informix product will default the century value to the present century, which it obtains from the computer's system clock. If the DBCENTURY variable is set, it can be set to select a default date that is in the past, in the future, or closest to the computer's system clock. Informix anticipates that the latest setting will be the most generally applicable for the next several decades. For example, if Current date is "06/21/1998" and user enters two dates, "10/25/95" and "08/01/03", dates stored is shown below:

    Table 1: Example of DBCENTURY settings

    DBCENTURY setting Dates Stored
    P 10/25/1995, 08/01/1903
    R 10/25/1995, 08/01/1903
    C 10/25/1995, 08/01/2003
    F 10/25/2095, 08/01/2003

    If the DBCENTURY variable is not set in the user's login profile, the dates would default to "10/25/1995" and "08/01/1903", the present century. If the user enters the full four digits for the years into a four-digit year field in the application, the year will be accepted as entered, regardless of the DBCENTURY variable setting.

    If the user enters into an application only two digits for the year, and the default century set through DBCENTURY is making an incorrect assumption on the range of dates to be entered, incorrect data will be stored and returned. For example, if a user enters '03 as shorthand for 1903, and the DBCENTURY variable is set to default to the closest date, the Informix product would store 2003. If the application is expected to manage dates which are more than 50 years in the past or in the future, in order to use DBCENTURY to best suit the needs of the application, please refer to the Informix documentation regarding the use of the various settings of DBCENTURY.

    MSSQL
    From Microsoft

    SQL Server allows users to specify only the last 2 digits of the year. However the year is still stored the same as if 4 digits were specified. The "two-digit year cutoff" configuration option defines how SQL Server 7.0 expands a 2-digit year to four digits. The option is specified as a 4-digit year, the default is 2049. If only two digits are specified for a year, and they are less than or equal to the last two digits of the 2-digit year cutoff option, the date is stored as being in the same century as the option. If the specified two digits are greater than the last two digits of the 2-digit year cutoff option, the date is stored as being in the century before the one specified in the option. For example, if the 2-digit year cutoff option is left at its default of 2049, the value 49 is stored as 2049 and 50 is stored as 1950. If the 2-digit year cutoff option is set to 2029, the value 29 is stored as 2029 and 30 is stored as 1930.

    DB2
    IBM offers a large amount of information on the year 2000. http://www.s390.ibm.com/vse/vsehtmls/vn13sql.htm#Header_3 indicates that two-digit dates that have a year less than 43 are assumed to be in the 21st century (20xx). Otherwise, they are assumed to be in the 20th century (19xx).

    Please see the IBMs year 2000 web site at IBM for additional information.

    ODBC
    Dates entered when connected via an ODBC driver are sent to the ODBC driver exactly as entered. If you enter a 4-digit year, a 4-digit year is sent. If you enter a 2-digit year, a 2-digit year is sent. The Server that the ODBC driver connects does interpretation of the date. If your ODBC driver connects to one the SQL Links drivers listed above, dates should be interpreted the same as the SQL Link driver. Otherwise, contact the manufacturer of the database that you connect to determine how 2-digits years are interpreted.

    Documentation from Microsoft on ODBC and year 2000


    Server Response from: ETNASC03