Incorrect nvarchar to date conversion in oracle

Question

I am trying to insert data having datatype as nvarchar to a column having data type as date and oracle is doing the conversion wrongly. I tried couple of things and it isn't working. Could some please suggest an alternative. I am using oracle 12cc

The source column have the value in the format 31-DEC-99 but when I use to_date to convert it, the value becomes 0099-12-31.

select to_date(EFCTV_DT,'yyyy-mm-dd') from table1;

Answer 1

You need to use correct format as follows:

to_date(EFCTV_DT,'dd-mon-rr')

Whenever you use the year with two digits, You have two options available with you as follows:

• rr - make the year falling in xx50-current_year-xx49
• yy - make the year falling in xx00-current_year-xx99

so the current year is 2020 and if you will use the 95 as the year then you will get the following:

rr - 1995
yy - 2095