How to change non date into date oracle

Question

I have a date value that is not a date value in the database. In the database it is a varchar2(6) and looks like this: 240416 (translates to 16.04.2024). My Question now is how can i change that format, when i make a query to a normal date format, as example DD/MM/YYYY?

Thanks to all in advance.

I tried to google an answer but did not find one.

Answer 1

As long as you know it's the current century you can do

select to_date('240416', 'yymmdd') from dual

From there on, it is handled like a normal date, which can be formatted either in your client or by other date handling function, like this

select mydate, 
       to_char(mydate, 'dd/mm/yyyy') formatteddate 
from (
    select to_date('240416', 'yymmdd') mydate 
    from dual
)