Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have the following queries:

SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL;  
/*this returns 2/1/2049. */

SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL; 
/*this returns 2/1/1949.*/

Why does the first one returns the year 2049 instead of 1949?

By Googling I have found that I can "force" the client date format to be the one desire by changing the keyon the registry:

KEY_OraClient11g_home1
NLS_DATE_FORMAT : YYYY/MM/DD

Thanks in advance!

share|improve this question
Why convert a string to a date, then to a string and back to a date again? What are you trying to achieve? – a_horse_with_no_name May 31 at 13:01

1 Answer

up vote 3 down vote accepted

Both functions TO_DATE and TO_CHAR take three arguments. The two last arguments take a default value if they are unspecified.

The second argument is the format. It will default to your NLS_DATE_FORMAT session parameter value. Since you will potentially lose information during the conversion, there is no reason to assume that the two functions are the inverse of one another.

Let's take an example with the default poor choice of format DD-MON-RR:

SQL> select to_date('01-FEB-1949', 'DD-MON-RR') d from dual;

D
------------------------------
01/02/1949

SQL> SELECT to_char(to_date('01-FEB-1949', 'DD-MON-RR'),
  2                 'DD-MON-RR') c
  3  FROM dual;

C
------------------------------
01-FEB-49

SQL> SELECT to_date(to_char(to_date('01-FEB-1949', 'DD-MON-RR'),
  2                         'DD-MON-RR'),
  3                 'DD-MON-RR') d
  4  FROM dual;

D
-----------
01/02/2049

This shows that we have lost data during the second step: the century was lost! The third step has to make up for it and uses the default rule of RR:

  • 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.

We're in the first case, the century chosen is the current century (20XX).

In conclusion:

  • Don't rely on the session parameters for conversion because they can be changed outside of your control, use explicit conversion rules.
  • Don't use the RR or YY format because a year has 4 digits (ever heard about the Y2K bug?)
  • While we're at it, don't use the MON month format, your application won't work if someone uses another regional NLS setting: months use distinct abbreviations in different languages!
share|improve this answer
Great explanation, thanks a lot! – Luis May 31 at 14:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.