Impala: convert dd-mm-yy hh:mm:ss string to Date yyyy-mm-dd HH:mm:ss.SSS format

Question

I have a impala table where date column values are stored in dd-mm-yy HH:mm:ss string format, e.g.

30-11-20 12:34:45

I want to convert them into yyyy-mm-dd HH:mm:ss.SSS, e.g.

2020-11-30 12:34:45.000

If anyone could suggest a way to achieve this!!

Answer 1

first use to_timestamp to convert to timestamp. Then use from_timestamp to convert to string.

select from_timestamp(
   to_timestamp('30-11-20 12:34:45','dd-MM-yy HH:mm:ss')
 ,'yyyy-MM-dd HH:mm:ss.SSS')  as str_timestamp