Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE employee ( date_registered ) AS
SELECT DATE '2020-05-09' + INTERVAL '02:40:03' HOUR TO SECOND FROM DUAL UNION ALL
SELECT DATE '2020-05-16' + INTERVAL '02:40:03' HOUR TO SECOND FROM DUAL UNION ALL
SELECT DATE '2020-05-15' + INTERVAL '02:40:03' HOUR TO SECOND FROM DUAL;
3 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD"T"HH24:MI:SS';
SELECT date_registered                          AS reg_date_with_nls_date_format,
       TRUNC( date_registered )                 AS truncated_reg_date,
       TO_CHAR( date_registered, 'DD/MM/YYYY' ) AS formatted_reg_date
FROM   employee


REG_DATE_WITH_NLS_DATE_FORMAT
TRUNCATED_REG_DATE
FORMATTED_REG_DATE


2020/05/09T02:40:03
2020/05/09T00:00:00
09/05/2020

2020/05/16T02:40:03
2020/05/16T00:00:00
16/05/2020

2020/05/15T02:40:03
2020/05/15T00:00:00
15/05/2020


CREATE TABLE employee ( date_registered ) AS
SELECT '2020/05/09T02:40:03' FROM DUAL UNION ALL
SELECT '2020/05/16T02:40:03' FROM DUAL UNION ALL
SELECT '2020/05/15T02:40:03' FROM DUAL;
ORA-00955: name is already used by an existing object

SELECT date_registered,
       TO_DATE( date_registered, 'YYYY/MM/DD"T"HH24:MI:SS' )
         AS reg_date_with_nls_date_format,
       TRUNC( TO_DATE( date_registered, 'YYYY/MM/DD"T"HH24:MI:SS' ) )
         AS truncated_reg_date,
       TO_CHAR( TO_DATE( date_registered, 'YYYY/MM/DD"T"HH24:MI:SS' ), 'DD/MM/YYYY' )
         AS formatted_reg_date
FROM   employee


DATE_REGISTERED
REG_DATE_WITH_NLS_DATE_FORMAT
TRUNCATED_REG_DATE
FORMATTED_REG_DATE


2020/05/09T02:40:03
2020/05/09T02:40:03
2020/05/09T00:00:00
09/05/2020

2020/05/16T02:40:03
2020/05/16T02:40:03
2020/05/16T00:00:00
16/05/2020

2020/05/15T02:40:03
2020/05/15T02:40:03
2020/05/15T00:00:00
15/05/2020


ALTER TABLE employee ADD ( date_registered2 DATE );
UPDATE employee
  SET date_registered2 = TO_DATE( date_registered, 'YYYY-MM-DD"T"HH24:MI:SS' );
3 rows affected

ALTER TABLE employee DROP COLUMN date_registered;
ALTER TABLE employee RENAME COLUMN date_registered2 TO date_registered;
SELECT date_registered                          AS reg_date_with_nls_date_format,
       TRUNC( date_registered )                 AS truncated_reg_date,
       TO_CHAR( date_registered, 'DD/MM/YYYY' ) AS formatted_reg_date
FROM   employee


REG_DATE_WITH_NLS_DATE_FORMAT
TRUNCATED_REG_DATE
FORMATTED_REG_DATE


2020/05/09T02:40:03
2020/05/09T00:00:00
09/05/2020

2020/05/16T02:40:03
2020/05/16T00:00:00
16/05/2020

2020/05/15T02:40:03
2020/05/15T00:00:00
15/05/2020

REG_DATE_WITH_NLS_DATE_FORMAT	TRUNCATED_REG_DATE	FORMATTED_REG_DATE
2020/05/09T02:40:03	2020/05/09T00:00:00	09/05/2020
2020/05/16T02:40:03	2020/05/16T00:00:00	16/05/2020
2020/05/15T02:40:03	2020/05/15T00:00:00	15/05/2020