'RRRR' versus 'YYYY' in the date format

The RRRR date format was provided to help people cope with Y2K issues. If the format is RR or RRRR and a two-digit year is given, then the year will be assumed to be prepended with '19' if the given year is between 50 and 99, or it will be assumed to be prepended with '20' if the given year is between 0 and 49. The RRRR format will accept both two and four digit years. If you give it a four digit year, no assumptions are made. This can be verified as seen below:

ORA9I SQL> select
TO_CHAR(TO_DATE('01/01/2093','MM/DD/RRRR'),'MM/DD/YYYY')
from dual;

TO_CHAR(TO
----------
01/01/2093

You can see that my four digit year, "2093" did not undergo any changes. Now, let's look at a two digit year with the four character RRRR format:

ORA9I SQL> select
TO_CHAR(TO_DATE('01/01/01','MM/DD/RRRR'),'MM/DD/YYYY')
from dual;

TO_CHAR(TO
----------
01/01/2001

Since my two digit year was '01', the RRRR format converted this to the year 2001. Let's see how it works with a different year.

ORA9I SQL> select
TO_CHAR(TO_DATE('01/01/93','MM/DD/RRRR'),'MM/DD/YYYY')
from dual;

TO_CHAR(TO
----------
01/01/1993

Since my two digit year was '93', the RRRR format converted this to the year 1993. Notice that this differs from the behavior of the 'YYYY' format:

ORA9I SQL> select
TO_CHAR(TO_DATE('01/01/01','MM/DD/YYYY'),'MM/DD/YYYY')
from dual;

TO_CHAR(TO
----------
01/01/0001

ORA9I SQL> select
TO_CHAR(TO_DATE('01/01/93','MM/DD/YYYY'),'MM/DD/YYYY')
from dual;

TO_CHAR(TO
----------
01/01/0093

No matter which year I gave it, it assumed to prepend with '00' for the century. Contrast this to how 'YY' behaves:

ORA9I SQL> select
TO_CHAR(TO_DATE('01/01/93','MM/DD/YY'),'MM/DD/YYYY')
from dual;

TO_CHAR(TO
----------
01/01/2093

The YY format assumed the current century.

For More Information

• Dozens more answers to tough Oracle questions from Brian Peasland are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.