Problem solve Get help with specific problems with your technologies, process and projects.

Confusion around PL/SQL to_date and to_char functions

One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values.

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


The output was 23-MAY-2001

Then I did this:


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

So, I changed it to the RR format:


But again the output was 23-MAY-2001

Can you please explain the conflict?

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:


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:


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 last published in October 2012

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.