• 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’.
• If the current year is in the second half of the century (50 - 99- 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: This will be stored as a current century year. E.g. ‘02’ entered in 2001 will be stored as ‘2002’.
• If the current year is in the first half of the century (00 - 49- 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’.

The ‘RR’ date format is available for inserting and updating DATE data in the database. It is not required for retrieval/query of data already stored in the database as Oracle has always stored the YEAR component of a date in it’s four digit form.

Further details concerning the ‘RR’ DATE format mask can be found in the Oracle Technical Bulletin Ref. 104296.626.

The ‘RR’ date format will only work where the user application utilises the Oracle DATE data type. Where applications use character strings in CHAR or VARCHAR2 data types, unless consideration was made at design tim e, the application will require modification to include routines to ensure that such dates are treated appropriately when affected by the change in century.

For new applications, or when carrying out the modifications to ensure dates stored as character strings are Year-2000 compliant, it is advisable convert such dates to use the Oracle DATE datatype, thus ensuring year 2000 compliance, or if this is not feasible then to store the dates in canonical form which is language and format independent, and which handles full years.

For example ‘YYYY/MM/DD’ , plus if necessary the time element as ‘hh24:mi:ss’. Dates stored in this form must be converted to the correct external format whenever they are displayed or received from users or other programs.

The format ‘YYYY/MM/DD HH24:MI:SS’ has the following advantages:

One disadvantage is that there is no support for dates BC

The Oracle 7 Server information presented by Oracle Server Manager is obtained from the RDBMS via internally issued SQL queries. Therefore, full compliance is inherent though the RDBMS compliance.

The Oracle ODBC drivers use ODBC date and time datatypes that all include a four digit year element

The Oracle 7 Server information presented by Oracle Enterprise Manager is obtained from the RDBMS via internally issued SQL queries.

Therefore, full compliance is inherent though the RDBMS compliance.

The Oracle Precompiler products are Year 2000 Compliant.

The Oracle Precompiler products do not have pre-defined routines for manipulating date data, all such manipulation is provided by the developer in the 3GL code and calls to the server-side PL/SQL engine.

Therefore, Year 2000 Date compliance is up to the developer to design and write the appropriate code.

Express Server and Personal Express are Year 2000 compliant. The internal mechanism for handling time dimensions and the 'date' data type are based on the number of seconds from a fixed base date. The year 2000 has no special meaning, and is treated exactly like any other date.

All functions for entering, storing, calculating, or displaying dates are identical to pre-2000 behavior

A second consideration is the handling of years in two-digit format, for example "97" to represent "1997." By default, a two-digit year is interpreted to be in the range 1950 to 2049, unless specified otherwise by means of the option YRABSTART. Thus "0 0" is interpreted, by default, as the year 2000.

Express correctly handles leap years. Given that every fourth year is a leap year, except century years (e.g, 1900) unless the year is divisible by 400 (e.g., 2000), Express treats the year 2000 as a leap year. A simple test is to create a time dimensi on, such as month, with values that include the month of February 2000. Using the function ENDOF(month), the result is 29FEB2000, demonstrating that 2000 is treated as a leap year.

Designer/2000 provides full support where the generator’s destination product has support for Year-2000 compliance. For example, when Developer/2000 modules are generated, full compliance is supported through th e ‘RR’ date format mask. When generating Visual Basic or C++ modules, compliance must be provided by the application developer/programmer. The most straight forward method of proving such compliance is by utilising four digit year fields.

The date related audit columns on the base tables used by Designer/2000 are maintained by database triggers. Therefore, the database takes care of this issue. Designer/2000 simply displays date information. The presentation of all dates is determined b y an ‘.ini’ file variable (M.S. Windows 3.1) or a registry variable (M.S. Windows NT/95) and the default is DD Month YYYY. However, the user can, of course, change it.

The products mentioned in this section are products that are known to be in use by some customers at the time of writing and are either no longer supported under Oraclemetals support or are due to removed from t hat category.

These products are not Year 2000 Compliant. However, a significant level of compliance can be achieved by the use of date fields incorporating a four digit year component. If fields incorporating a two digit year co mponent are used, then in order to ensure that the appropriate century is used when saving new or changed date data to the underlying database a workaround using a trigger would be necessary that calls a user written procedure or function that applies the appropriate algorithm.

One suggested workaround is:

In SQL*Forms 3.0, do the following to the DATE field:

N.B. this code reformats the DATE field named hiredate.

To display 4 digit year in Forms3, create a procedure:

drop procedure fix_date; 
create procedure fix_date (date_field in out date)   is 
      date_field_yr  number(4) :=
to_number(to_char(date_field,'YYYY'));  
      date_field_yr2 number(2) :=
to_number(to_char(date_field,'YY'));  
      date_work      char(11);  
      begin  
      if date_field_yr < 100 then  
       if date_field_yr2 > 50 then  
        date_work := to_char(date_field, 'DD-MON-')||  
                     '19'||  
                     lpad(to_char(date_field_yr),2,'0');  
       else  
        date_work := to_char(date_field, 'DD-MON-')||  
                     '20'||  
                     lpad(to_char(date_field_yr),2,'0');  
       end if;  
       date_field := to_date(date_work, 'DD-MON-YYYY');  
      end if;  
      end;  
/ 
show errors procedure fix_date; 
set serveroutput on 
execute fix_date('3-jan-97'); 
execute fix_date('3-jan-01'); 

Oracle Customer Support can provide Technical Bulletins that provides details of the use of the ‘RR’ year format mask.

This product is not Year 2000 Compliant.

This product is not Year 2000 Compliant.

This product is not Year 2000 Compliant

This product is partially Year 2000 Compliant. However, the only reason why it cannot be classified as fully compliant is due to the omission of a programmatic solution for defaulting the century when dates with a two digit year element are used (i.e. the ‘RR’ algorithm implemented in the Oracle7 Server is not available in the Oracle RDBMS Version 6.

Like the Oracle 7 Server, the Oracle RDBMS Version 6 DATE datatype stores date and time data to a precision that includes a four digit year and a time component down to seconds (typically ‘YYYY:MM:DD:HH24:MI:SS’).