The RR element

The recent millennium change caused an explosion of interest in using four-digit years as people suddenly realized the ambiguity inherent in the commonly used two-digit year. For example, does 1-Jan-45 refer to 1945 or 2045? In spite of this realization, habits are tough to break and existing systems can be difficult to change, so you may find yourself still needing to allow your users to enter dates using two-digit years rather than four-digit years.

Using two-digit years is especially problematic when the century and millennium are close to changing. The YY format element always defaults to the current century, so when it is November 1999 and your user enters 1/1/1 or 1-JAN-1, they will enter into the database the date of January 1, 1901, not January 1, 2001.

What's an information systems manager to do? One solution is to go into all your screens and change or add trigger logic so that if the user enters a year number less than ten (or whatever you decide the cutoff to be), then the next century will be assumed. That would work, but it's a very undesirable prospect.

Fortunately, Oracle provides a format element to take care of this problem: the RR format model. Here is how RR works.

In the following discussion, we use the term "century" colloquially. RR's 20th century is composed of the years 1900-1999, and its 21st century is composed of the years 2000-2099. We realize this is not the proper definition of century, but it's a definition that makes it easier to explain RR's behavior.

If the current year is in the first half of the century (years 0 through 49), then:

· If you enter a date in the first half of the century (i.e., from 0 through 49), RR returns the current century.

· If you enter a date in the latter half of the century (i.e., from 50 through 99), RR returns the previous century.

On the other hand, if the current year is in the latter half of the century (years 50 through 99), then:

· If you enter a date in the first half of the century, RR returns the next century.

· If you enter a date in the latter half of the century, RR returns the current century.

Confusing? We had to think about it for awhile too. The RR rules are an attempt to make the best guess as to which century is intended when a user leaves off that information. Here are some examples of the impact of RR. Notice that for year 88 and year 18, SYSDATE returns a current date in the 20th and 21st centuries, respectively:

SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') "Current Date", TO_CHAR (TO_DATE ('14-OCT-88', 'DD-MON-RR'), 'YYYY') "Year 88", TO_CHAR (TO_DATE ('14-OCT-18', 'DD-MON-RR'), 'YYYY') "Year 18" FROM dual; Current Date Year 88 Year 18------------ ------- ------- 02/25/2002 1988 2018

When we reach the year 2050, RR will interpret the same dates differently:

SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') "Current Date", TO_CHAR (TO_DATE ('10/14/88', 'MM/DD/RR'), 'YYYY') "Year 88", TO_CHAR (TO_DATE ('10/14/18', 'MM/DD/RR'), 'YYYY') "Year 18" FROM dual; Current Date Year 88 Year 18------------ ------- ------- 02/25/2050 2088 2118

Of course, if you use the RR format now and want to enter a date that falls in the latter half of the 21st century (e.g., entering 75 for 2075), you will need to add special logic to your code. Masks with the RR format model will convert such two-digit years into the previous century until we reach the year 2050. Likewise, you'll need special logic if you wish to enter dates that fall in the early 20th century, as RR currently recognizes years 00-49 as part of the 21st century.

There are a number of ways you can activate the RR logic in your current applications. The cleanest and simplest way is to change the default format mask for dates in your database instance(s). You can do this by changing the NLS_DATE_FORMAT initialization parameter as follows:

NLS_DATE_FORMAT = 'MM/DD/RR'

or:

NLS_DATE_FORMAT = 'DD-MON-RR'

depending on what the previous format was. Then, if you have not hardcoded the date format mask anywhere else in your screens or reports, you are done. Bring down and restart the database, and then your application will allow users to enter dates in the 21st century. If you do have date format masks in the format property for an Oracle Forms item or in an Oracle Reports query or field, you will need to change those modules to reflect the new approach embodied by RR.