select to_char(t.create_time,'yyyy-mm-dd')as date_,remark,
       substr(t.remark, 1, 4) as yyyy,
       substr(t.remark, 5, 2) as dd,
       (case
         when substr(t.remark, 7, 2) = '00' then
          '01'
         else
          substr(t.remark, 7, 2)
       end) as dd
       
       ,
       (substr(t.remark, 1, 4)||'-'||substr(t.remark, 5, 2)||'-'||
       (case
         when substr(t.remark, 7, 2) = '00' then
          '01'
         else
          substr(t.remark, 7, 2)
       end))as cjd
  from temp_xxx t;

update temp_xxx t1 set t1.create_time=to_date(
substr(t1.remark, 1, 4)||substr(t1.remark, 5, 2)||
       (case
         when substr(t1.remark, 7, 2) = '00' then
          '01'
         else
          substr(t1.remark, 7, 2)
       end)
,'yyyymmdd'
);