Oracle PL/SQL RR Datetime Format

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year

If the two digits of the current year are :		If the Specified two digit year is
		00-49				50-99
		00-49	The return date is in the current century			The return date is in the century before the current one
		50-99	The return date is in the century after the current one			The return date is in the current century

• If the specified two-digit year is 00 to 49, then
  • If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  • If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
• If the specified two-digit year is 50 to 99, then
• If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
• If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year

Assume these queries are issued between 1950 and 1999

Example 1:

SELECT TO_CHAR(TO_DATE('09-MAR-95', 'DD-MON-RR') ,'YYYY') "Year"  FROM DUAL;

YEAR
-----------
1995

Example 2:

SELECT TO_CHAR(TO_DATE('09-MAR-15', 'DD-MON-RR') ,'YYYY') "Year"   FROM DUAL; 

YEAR
-----------
2015

Assume the above queries are run between 2000 and 2049

Example 1:

SELECT TO_CHAR(TO_DATE('09-MAR-95', 'DD-MON-RR') ,'YYYY') "Year"  FROM DUAL;

YEAR
-----------
1995

Example 2:

SELECT TO_CHAR(TO_DATE('09-MAR-15', 'DD-MON-RR') ,'YYYY') "Year"   FROM DUAL; 

YEAR
-----------
2015

Oracle PL/SQL - TO_CHAR function

TO_CHAR Function : 

Syntax : TO_CHAR (input_value, fmt_mask, nls_language); 

• input_value can be either a string,number or a interval of datetime.
• Return type is VARCHAR2
• fmt_mask is the format type to which the input value is converted. This parameter is optional.
• nls_language determines the language in which the month,day and other abbreviated strings are returned. This parameter is optional.

Example 1:

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') FROM DUAL;

RESULT
-----------
2015/03/09

Example 2:

SELECT TO_CHAR(SYSADTE, 'Month DD, YYYY') FROM DUAL;

RESULT
-----------
March 09, 2015

Parameter
Explanation
YYYY
 4-digit year
YEAR
Year, spelled out
YYY
YY
Y
Last 3, 2, or 1 digit(s) of year.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
4-digit year based on the ISO standard
Q
Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM
Month (01-12; JAN = 01).
MON
Abbreviated name of month.
MONTH
Name of month, padded with blanks to length of 9 characters.
RM
Roman numeral month (I-XII; JAN = I).
WW
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh

day of the year.
W
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW
Week of year (1-52 or 1-53) based on the ISO standard.
D
Day of week (1-7).
DAY
Name of day.
DD
Day of month (1-31).
DDD
Day of year (1-366).
DY
Abbreviated name of day.
J
Julian day; the number of days since January 1, 4712 BC. specified with J must be integers
HH
Hour of day (1-12).
HH12
Hour of day (1-12).
HH24
Hour of day (0-23).
MI
Minute (0-59).
SS
Second (0-59).
FF 
Fractional seconds.
 SSSSS
Seconds past midnight (0-86399)