convert multiple date format into one format-CodePudding

I would like to know the answer
I have this kind of problem,
I have one column that consist 2 date format but in string format

Create_Date
05-May-2021 17:03:58
19-10-2020 10:16:26
09/10/2019
28-03-2020 10:16:26
05-June-2020 16:23:17
20/12/2020

and then I would like to convert it to default datetime format, so it would be like this

Create_Date
2021-05-05
2020-10-19
2019-10-09
2020-03-28
2020-06-05
2020-12-20

I've tried many ways but cant find the solution,

CodePudding user response：

Consider below query

with sample_data as (
  select '05-May-2021 17:03:58' Create_Date union all
  select '19-10-2020 10:16:26' union all
  select '09/10/2019' union all
  select '28-03-2020 10:16:26' union all
  select '05-June-2020 16:23:17' union all
  select '20/12/2020'
)
select from_unixtime(coalesce(
         unix_timestamp(Create_Date, 'dd-MMM-yyyy HH:mm:ss'),
         unix_timestamp(Create_Date, 'dd-MM-yyyy HH:mm:ss'),
         unix_timestamp(Create_Date, 'dd/MM/yyyy')
       ), 'YYYY-MM-dd') Create_Date
  from sample_data;

query results