Wednesday, June 11, 2014

Difference Between ‘YYYY’ and ‘RRRR’ in Date Format

--> Examine the below sql statement carefully we are trying to fetch full year from the date.

SELECT to_char(to_date('20-MAR-77','DD-MON-YY'),'YYYY') FROM dual

--> What will be the result of the query? Should it be ‘1977’ or ‘2077’ or ‘0077’.
--> If we give a four digit year then no assumptions are made. The issue is all with 2 digit years.
--> For this reason Oracle has introduced ‘RRRR’ format to help people cope up with Y2K issues If the format is RR/RRRR and a two-digit year is given, then 2-digit years in the range 00 to 49 are assumed to have the same first two digits as the current year, and years given as 50 through 99 are assumed to be in the previous century. That is if the given 2-digit year is between 50 and 99 then the year will be assumed to be ‘19’.

--> FOR ‘RR’
SELECT to_char(to_date('20-MAR-77','DD-MON-RR'),'YYYY') FROM dual
--> Output
--> -----------
--> 1977
   
--> FOR ‘RRRR’(same AS RR format)
SELECT to_char(to_date('20-MAR-77','DD-MON-RRRR'),'YYYY') FROM dual
--> Output
--> -----------
--> 1977

--> FOR ‘YY’
SELECT to_char(to_date('20-MAR-77','DD-MON-YY'),'YYYY') FROM dual
--> Output
--> -----------
--> 2077

--> FOR ‘YYYY’
SELECT to_char(to_date('20-MAR-77','DD-MON-YYYY'),'YYYY') FROM dual
-->Output
--> -----------
--> 0077

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect