Oracle 21c

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

CREATE TABLE My_Table (My_Column) AS
SELECT '120081212' FROM DUAL;
1 rows affected

SELECT my_column,
       TO_CHAR(
         TO_DATE(
           SUBSTR(My_Column,2,8),
           'yyyymmdd'
         ),
         'YYYY-MM-DD'
       ) AS formatted_value
FROM   My_Table;


MY_COLUMN
FORMATTED_VALUE


120081212
2008-12-12


SELECT my_column,
       TO_DATE(
         SUBSTR(My_Column,2,8),
         'yyyymmdd'
       ) AS date_value
FROM   My_Table;


MY_COLUMN
DATE_VALUE


120081212
12-DEC-08