to_date on a date gives wrong answer
Hi, before people start saying why do you do a to_date on a date, yes we should not but we did.
what happens (both in oracle 12.1 and 19.16):
given a nls_date_format like dd-mm-yy and one executes a to_date on a date using dd-mm-yyyy as the format to use, we loose the century:
example code:
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yy';
select to_char(to_date(sysdate,'dd-mon-yyyy'),'dd-mm-yyyy') wrong from dual;
result:
WRONG
----------
25-04-0023 >>>>>>>>>>>>>>>>>>>>>>>>>>>>
It gave the correct result in January.
Can any body answer with a database version where it gives 2023 instead of 0023