Q
Problem solve Get help with specific problems with your technologies, process and projects.

'RRRR' versus 'YYYY' in the date format

What are the advantages and disadvantages of using "RRRR" in the date format instead of using "YYYY"?
 

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


 

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close