What is the difference between oracle's 'yy' and 'rr' date mask?

Question

Example:

select ename from emp where hiredate = todate('01/05/81','dd/mm/yy')

and

select ename from emp where hiredate = todate('01/05/81','dd/mm/rr')

return different results

Answer 1

http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/difference-between-yyyy-and-rrrr-format-519525

YY allows you to retrieve just two digits of a year, for example, the 99 in 1999. The other digits (19) are automatically assigned to the current century. RR converts two-digit years into four-digit years by rounding.

50-99 are stored as 1950-1999, and dates ending in 00-49 are stored as 2000-2049. RRRR accepts a four-digit input (although not required), and converts two-digit dates as RR does. YYYY accepts 4-digit inputs butdoesn't do any date converting

Essentially, your first example will assume that 81 is 2081 whereas the RR one assumes 1981. So the first example should not return any rows as you most likely did not hire any guys after May 1 2081 yet :-)

Answer 2

y2k compatibility. rr assumes 01 to be 2001, yy assumes 01 to be 1901

see: http://www.oradev.com/oracle_date_format.jsp

edit: damn! michael "quickfingers" stum beat me to it!

/mp

Answer 3

Will yy always assume 19xx? My last Oracle experience is a bit long ago, but I thought YY just takes the current century, i.e. 20xx?

Answer 4

@Michael Stum

My last Oracle experience is a bit long ago

uhm, was it, before 2000? :p

...

Will yy always assume 19xx?

according to your source, we get the following scenarios:

USING
ENTERED
STORED
SELECT of date column


YY
22-FEB-01
22-FEB-1901
22-FEB-01


YYYY
22-FEB-01
22-FEB-0001
22-FEB-0001


RR
22-FEB-01
22-FEB-2001
22-FEB-01


RRRR
22-FEB-01
22-FEB-2001
22-FEB-2001

/mp

Answer 5

RR stands for after 1990 and yy assumes 90 as 2090....as we are in the current yr,...