I have been searching around for a while now, but I can't seem to find the answer to this small problem.

How to convert string 08-JUL-06 to datetime 08/07/1906? I've tried with code like the following:

select to_char(to_date('08-jul-06', 'dd-mon-rr'), 'dd/mm/yyyy') result from dual;

However, the result shows wrong: to be 08/07/2006? How do I solve the problem?

CodePudding user response：

RR format is not suitable for this(*). How would Oracle know that you meant 1900s not 2000s? You know it because you know the context in which they occur. I suggest converting it to text and add 19 in front of the year yourself, like:

select to_date('19'||to_char(to_date('08-jul-06', 'dd-mon-yy'), 'yymmdd'),'yyyymmdd') result 
from dual;

It is now a date, you can view it in the format you mentioned using to_char(...'dd-mon-yyyy'); or it might readily display like that if your Oracle installation date-format is that.

(*) 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.

CodePudding user response：

How to convert string 08-JUL-06 to datetime 08/07/1906?

You cannot, Oracle has two format models for handling two-digit years: YY and RR.

• YY will assume the century is the current century.
• RR will assume the century is:
  • The previous century if the specified year is 50-99 and the current year is 0-49
  • The current century if the specified year is 50-99 and the current year is 50-99
  • The current century if the specified year is 0-49 and the current year is 0-49
  • The next century if the specified year is 0-49 and the current year is 50-99

In none of these options will 06 assume that the full 4-digit year is 1906.

What you need to do is use a 4-digit year:

SELECT TO_CHAR(DATE '1906-07-08', 'dd/mm/yyyy') AS result FROM DUAL;

or, change your string to insert the century:

SELECT TO_CHAR(
         TO_DATE(
           SUBSTR(value, 1, 7) || '19' || SUBSTR(value, 8),
           'DD-MON-YYYY',
           'NLS_DATE_LANGUAGE=English'
         ),
         'dd/mm/yyyy'
       ) AS result
FROM   (SELECT '08-JUL-06' AS value FROM DUAL);

Which both output:

RESULT
08/07/1906

db<>fiddle here