Hmmmmm
SQL> create table t ( id int, x date );
Table created.
SQL> alter session set nls_date_format = 'DD-MON-RR';
Session altered.
SQL> insert into t values ( 1,'01-JUL-20');
1 row created.
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
SQL> alter session set nls_date_format = 'DD-MON-YYYY';
Session altered.
SQL> insert into t values (2,'01-JUL-20');
1 row created.
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
2 0020-07-01
SQL>
SQL> update T
2 set
3 x=to_date(to_char( to_date( x, 'dd-mm-rr' ), 'YYYY' ) || '/' || to_char( to_date( x, 'dd-mm-rr' ), 'MM/DD' ),'YYYY/MM/DD')
4 where x IS NOT NULL;
2 rows updated.
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
2 0020-07-01
Maybe something like this
SQL>
SQL> create table t ( id int, x date );
Table created.
SQL> alter session set nls_date_format = 'DD-MON-RR';
Session altered.
SQL> insert into t values ( 1,'01-JUL-20');
1 row created.
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
SQL> alter session set nls_date_format = 'DD-MON-YYYY';
Session altered.
SQL> insert into t values (2,'01-JUL-20');
1 row created.
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
2 0020-07-01
SQL>
SQL> alter session set nls_date_format = 'DD-MON-RR';
Session altered.
SQL> insert into t values ( 3,'01-JUL-95');
1 row created.
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
2 0020-07-01
3 1995-07-01
SQL> alter session set nls_date_format = 'DD-MON-YYYY';
Session altered.
SQL> insert into t values (4,'01-JUL-95');
1 row created.
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
2 0020-07-01
3 1995-07-01
4 0095-07-01
SQL>
SQL> update T
2 set x = add_months(x,2000*12)
3 where x between date '0001-01-01' and date '0030-01-01';
1 row updated.
SQL>
SQL> update T
2 set x = add_months(x,1900*12)
3 where x between date '0031-01-01' and date '0100-01-01';
1 row updated.
SQL>
SQL> select id, to_char(x,'YYYY-MM-DD') from t;
ID TO_CHAR(X,
---------- ----------
1 2020-07-01
2 2020-07-01
3 1995-07-01
4 1995-07-01
SQL>
SQL>
SQL>