We all know that we should not use TWO DIGIT YEAR formats in database applications since more than a decade.
And we also know that we should never rely on implicit datatype conversion especially with dates.
I recently came across a very surprising effect with Oracle 11g R2 Enterprise Edition when I was testing with two digit years:
I convert a string with two digit year information into a date and then I convert this date into a string with four digit year format to see what the server made.
SQL> SELECT TO_CHAR(TO_DATE('07-11-58', 'dd-mm-yy'), 'dd-mm-yyyy')
FROM dual;
TO_CHAR(TO
----------
07-11-2058
The result is as expected: the ‘YY’ interpreted the ’58′ as belonging to the current century.
SQL> SELECT TO_CHAR(TO_DATE('07-11-58', 'dd-mm-rr'), 'dd-mm-yyyy')
FROM dual;
TO_CHAR(TO
----------
07-11-1958
Using the ‘RR’ format also does what what we expect, since 58 is more than 49 and we currently have the year 2012 which is less than 2050, the two digits of the year are interpreted as belonging to the past century.
So far so good and I agree with the server, but have a look at this:
SQL> SELECT TO_CHAR(TO_DATE('07-11-1958', 'dd-mm-rr'), 'dd-mm-yyyy')
FROM dual;
TO_CHAR(TO
----------
07-11-1958
I input a four digit year string and tell the server that it is a two digit year format. I am very surprised that the server is able to handle this anyway at all !!
And now let’s see what the server does with this:
SQL> SELECT TO_CHAR(TO_DATE('07-11-1958', 'dd-mm-yy'), 'dd-mm-yyyy')
FROM dual;
TO_CHAR(TO
----------
07-11-1958
This is strange, but obviously a feature, pls see the comment
Posted by Lutz Hartmann 
