Q

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:

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?

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close