I am somewhat confused regarding PL/SQL to_date and to_char functions. See the following:

SELECT TO_CHAR(TO_DATE(TO_CHAR(TO_DATE('23-MAY-2001','DD-MON-YYYY'),'DD-MON-YY'),'DD-MON-YY'),'DD-MON-YYYY') FROM DUAL;

The output was 23-MAY-2001

Then I did this:

SELECT TO_CHAR(TO_DATE(TO_CHAR(TO_DATE('23-MAY-1901','DD-MON-YYYY'),'DD-MON-YY'),'DD-MON-YY'),'DD-MON-YYYY') FROM DUAL;

But the output was the same: 23-MAY-2001

So, I changed it to the RR format:

SELECT TO_CHAR(TO_DATE(TO_CHAR(TO_DATE('23-MAY-1901','DD-MON-RRRR'),'DD-MON-RR'),'DD-MON-RR'),'DD-MON-RRRR') FROM DUAL;

But again the output was 23-MAY-2001

Can you please explain the conflict?

    Requires Free Membership to View

Regarding the first statement, '23-MAY-2001' is a string. It might look like a date, but don't confuse date strings with actual DATE (the data type) values. Let's break PL/SQL to_date and to_char expressions down, starting with the inner-most expression and working outward. On the first expression, we start with this:

select TO_DATE('23-MAY-2001','DD-MON-YYYY') from dual;

This returns a DATE value of 05/23/2001 (as you'd expect), given the full year. We move to this:

select TO_CHAR(TO_DATE('23-MAY-2001','DD-MON-YYYY'),'DD-MON-YY') from dual;

This actually returns a STRING '23-MAY-01.' Is this 1901 or 2001? Well, it's neither. It's a STRING value, not a DATE. Next:

select TO_DATE(TO_CHAR(TO_DATE('23-MAY-2001','DD-MON-YYYY'),'DD-MON-YY'),'DD-MON-YY') from dual;

This returns the DATE 05/23/2001, as YY assumes current century.

Now let's take a look at the second expression:

SELECT TO_CHAR(TO_DATE(TO_CHAR(TO_DATE('23-MAY-1901','DD-MON-YYYY'),'DD-MON-YY'),'DD-MON-YY'),'DD-MON-YYYY') FROM DUAL;

We don't have to break this down to see that converting it from a DATE to a STRING and then back to DATE will result in the expression still assuming the current century, and therefore the output being 2001 rather than 1901.

Let's now look at the expression where you change it to the RR format and break that down:

SELECT TO_CHAR(TO_DATE(TO_CHAR(TO_DATE('23-MAY-1901','DD-MON-RRRR'),'DD-MON-RR'),'DD-MON-RR'),'DD-MON-RRRR') FROM DUAL;

First this:

select TO_DATE('23-MAY-1901','DD-MON-RRRR') from dual;

This returns a DATE 05/23/1901 as expected. Then:

select TO_CHAR(TO_DATE('23-MAY-1901','DD-MON-RRRR'),'DD-MON-RR') from dual;

Again, this returns a STRING '23-MAY-01', due to using RR as the output instead of RRRR. So again, no century can be assumed. Finally:

select TO_DATE(TO_CHAR(TO_DATE('23-MAY-1901','DD-MON-RRRR'),'DD-MON-RR'),'DD-MON-RR') from dual;

More Oracle PL/SQL expert advice:

See all of Daniel Clamage's PL/SQL advice

Ask your own question for SearchOracle.com experts

This returns a DATE 05/23/2001 -- the RR date format has "windowing" behavior. Check the Oracle document on this topic, but basically if the specified two-digit year is 00 to 49 (which it is in this case) and if the last two digits of the current year are 00 to 49 (which is also true), then the returned year has the same first two digits as the current year. This is what you saw. Because the last two digits of 2001 are between 00 and 49 and the last two digits of the current year, 2012, are also between 00 and 49, the returned year in your expression is 2001.

Some takeaways from this example:

  • Always use explicit DATE conversion routines; never rely on implicit conversion/casting.
  • Always use four-digit years with YYYY date format.
  • When in doubt, refer to the documentation but do some experimentation (as you did). The docs aren't always 100% accurate or can be vague, misunderstood or leave gaps or generate more questions than they answer.
  • Lastly, the behavior of RR is going to be different in 2050 than in 2049. Somebody else will be maintaining your code. Do not make them curse you!

This was first published in October 2012

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: