Menu
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
253
In 'YY' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx00 and ending with the next xx99. In 'RR' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx50 and ending with the next xx49.
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.
it means if you enter to_date( '01-jan-40', 'dd-mon-rr' ) Oracle will slide around the date based on the current year. In 1999 and 2001 -- that would be the year 2040. As opposed to yy -- where the century is based on the current date. meaning. to_date( '01-jan-40', 'dd-mon-yy' )
Oracle stores dates in an internal numeric format representing the century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY.
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 :-)
199
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
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
