## Question and Answer

## You Asked

I have a case where RR date format is not returning expected result... to_date('960504','rrmmdd') returns 2096, not 1996.

I seem to recall back in Y2k an inti parm that influenced the action of the RR format.... but I can't find it.

Am I imagining this?

I seem to recall back in Y2k an inti parm that influenced the action of the RR format.... but I can't find it.

Am I imagining this?

## and Connor said...

Sorry...I think it's your imagination :-) RR has always been about first versus second *half* of the century.

From the docs:

http://docs.oracle.com/database/122/SQLRF/Format-Models.htm#GUID-6C75461E-2E18-4C35-9EB4-038A7E1C9C1F

But I'd like to see you entire test case, because I dont see what you're seeing:

From the docs:

http://docs.oracle.com/database/122/SQLRF/Format-Models.htm#GUID-6C75461E-2E18-4C35-9EB4-038A7E1C9C1F

*The RR Datetime Format Element*

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.

That is:

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.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.

That is:

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.

But I'd like to see you entire test case, because I dont see what you're seeing:

SQL> alter session set nls_date_format = 'DD/MM/YYYY'; Session altered. SQL> select to_date('960504','rrmmdd') from dual; TO_DATE('9 ---------- 04/05/1996

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

Thanks

Thank you very much for the post and the answer: this is exactly what I was looking for!

glad we could help