Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: August 17, 2017 - 3:21 pm UTC

Last updated: June 14, 2024 - 3:25 am UTC

Version: 11.2.0.4 (maybe older?)

Viewed 10K+ times! This question is

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?

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

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.


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

  (2 ratings)

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

Comments

John Low, August 18, 2017 - 11:29 am UTC

Thanks

Developer

Boris Goldberg, June 13, 2024 - 5:14 pm UTC

Thank you very much for the post and the answer: this is exactly what I was looking for!
Connor McDonald
June 14, 2024 - 3:25 am UTC

glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.