Oracle 21c

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

create table school (col_date) as select 20221203 from dual;
1 rows affected

select to_date(col_date, 'YYYYMMDD')
from school

TO_DATE(COL_DATE,'YYYYMMDD')

03-DEC-22

select to_date(to_char(col_date), 'YYYYMMDD')
from school

TO_DATE(TO_CHAR(COL_DATE),'YYYYMMDD')

03-DEC-22

select to_char(to_date(to_char(col_date), 'YYYYMMDD'), 'YYYY-MM-DD')
from school

TO_CHAR(TO_DATE(TO_CHAR(COL_DATE),'YYYYMMDD'),'YYYY-MM-DD')

2022-12-03

select to_char(col_date, 'FM0000G00G00', 'nls_numeric_characters='' -''')
from school

TO_CHAR(COL_DATE,'FM0000G00G00','NLS_NUMERIC_CHARACTERS=''-''')

2022-12-03