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,
Advertisement
Answer
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;