Differencebetween Oracle's 'Yy' and 'Rr' Date Mask

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

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 :-)

What is the difference between 'YYYY' and 'RRRR' in Oracle SQL

YYYY gives the current year as 4 digits.

RRRR format means 2-digit years in the range 00 to 49 are assumed to be in the current century (ie 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.

What does RR represent in date format oracle 10g?

To quote the Oracle documentation:

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.

Source: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#SQLRF00215

So basically it allows you to only specify 2 numbers of the year and Oracle will handle the century for you. In the documentation, you will also find the logic behind this. Another use is the use in queries:

The RR datetime format element lets you write SQL statements that will return the same values from years whose first two digits are different.

Oracle PL/SQL: how the SYSDATE is different from 'DD-MMM-YY'?

In Oracle, a DATE value - despite the name - contains a time part as well. SYSDATE contains the current date and the current time (up to seconds).

The Oracle tools by default (stupidly) hide the time part of a DATE value. If you run:

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as sysdate
from dual;

you can see that.

So SYSDATE might be 2018-04-27 09:15:42 whereas the string (!) constant '27-APR-18' is silently converted to a DATE value at midnight: 2017-04-28 00:00:00

More details in the chapter Basic Elements of Oracle SQL in the manual

---

If you don't care about the time part, you can use trunc() to set the time to midnight, trunc(sysdate) yields 2018-04-27 00:00:00 (if today is 2018-04-27). Note that trunc() does not "remove" the time, it only sets it to 00:00:00

---

Unrelated, but:

You should never rely on implicit casting between strings and other non-character types which var2 := '27-APR-18' does - it would e.g. fail on my computer as my default NLS date format is different.

If you need a DATE value, then specify a proper date literal:

var2 := DATE '2018-04-27'; 

or

var2 := to_date('27-APR-18', 'dd-mon-rr');

or

var2 := to_date('27-APR-18 00:00:00', 'dd-mon-rr hh24:mi:ss');

Convert Oracle to_date('24-03-20','DD-MM-RR') to snowflake

Be careful with YY, as RR in Oracle behaved differently than Snowflake's YY. RR in Oracle basically made two digit years into a "closest to the year 2000" model, e.g. RR string of 49 gave you 2049, RR string of 51 gave you 1951.

If you need RR "functionality", see the reference to the TWO_DIGIT_CENTURY_START session parameter at this link:

https://docs.snowflake.com/en/sql-reference/functions-conversion.html#date-and-time-formats-in-conversion-functions

That parameter is further defined here:

https://docs.snowflake.com/en/sql-reference/parameters.html#label-two-digit-century-start

So, you could use YY, but you'll need to run an ALTER SESSION command before you execute your select, examples as follows:

ALTER SESSION SET TWO_DIGIT_CENTURY_START = 1970; --the default
SELECT to_date('24-03-20', 'DD-MM-YY');           --2020-03-24

ALTER SESSION SET TWO_DIGIT_CENTURY_START = 1900; --not what you want
SELECT to_date('24-03-20', 'DD-MM-YY');           --1920-03-24

ALTER SESSION SET TWO_DIGIT_CENTURY_START = 1950; --RR like
SELECT to_date('24-03-20', 'DD-MM-YY');           --2020-03-24
SELECT to_date('24-03-49', 'DD-MM-YY');           --2049-03-24
SELECT to_date('24-03-50', 'DD-MM-YY');           --1950-03-24

I hope this helps...Rich

Oracle's default DATE format

Oracle, as well as other databases, allows you to set the default format. Out of the box, the format is (typically) DD-MON-RR, where "RR" refers to a two-digit year. This is a pretty lousy format, from the perspective of ambiguity (two digit year?) and internationalization (for what countries is that actually the default?). But Oracle has been around a long, long time.

Standard formats are also defined by ISO, the International Standards Organization. They settled on something more like YYYY-MM-DD. Actually, the hyphens are optional, but I think they make the date much more readable.

Oracle accepts constants in this format, if you use DATE:

select DATE '2018-01-25'

This is very handy. First, it is nice to support reasonable standards. Second, the code is safe, regardless of internationalization settings. Oracle documentation of course covers this in detail; here is one place to start.

---

Related Topics