I need to convert "my\_date" [ date type of column : VARCHAR2(4000 BYTE) ] column into date format as below, my table name "PROF\_DATA" 

```
my_date     my_date (need to convert as below)
-------    ----------
44849      15/10/2022
43890      29/02/2020
44196      31/12/2020
43769      31/10/2019
41517      31/08/2013
41501      15/08/2013

```

code which I tried

```
select CONVERT(DATETIME, substr(substr(RIGHT('0'+[my_date],6),3,0,'/'),6,0,'/'), 3) as chk_date
from PROF_DATA

********

select TO_CHAR(TO_DATE(my_date,'DD-MON-YY'), 'DD-MON-YY')
from PROF_DATA

```

But above oracle sql doesn't work ,
how should I write the code ?