Oracle 18c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI';
CREATE TABLE table_name (column_name VARCHAR2(10));
INSERT INTO table_name (column_name)
SELECT '1970-01-01' FROM DUAL UNION ALL
SELECT '1999-12-31' FROM DUAL UNION ALL
SELECT '2024-04-29' FROM DUAL;
3 rows affected

ALTER TABLE table_name ADD column_name_dt DATE;
UPDATE table_name
SET column_name_dt = TO_DATE(column_name, 'YYYY-MM-DD');
3 rows affected

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME COLUMN column_name_dt TO column_name;
SELECT * FROM table_name;


COLUMN_NAME


01-JAN-1970 00:00

31-DEC-1999 00:00

29-APR-2024 00:00