Impala DATE FORMAT

user_17861788 0
Viewed 1632

I have a impala table where report_date column values are stored in yyyyMMdd and yyyy-MM-dd string format, e.g.

20210923 2021-09-23

I want to convert them into DATE FORMAT

I tried below two commands to change the data type of the column from string to date

CAST(report_date AS DATE FORMAT 'yyyy-MM-dd')

CAST((CAST(CONCAT_WS('-',substr(repport_date,1,4),substr(repport_date,5,2),substr(repport_date,7)) AS STRING)) AS DATE FORMAT 'yyyy-MM-dd')

And this is working fine in one environment but throwing error in other environment.

Is there any other way to change string columns to date.

If anyone could suggest a way to achieve this!!

impala
1 Answers

You can try below code

select trunc(to_timestamp( replace(your_col,'-',''),'yyyyMMdd'),'DD') date

This will replace - with blank first so yyyy-MM-dd gets converted to yyyyMMdd. Then use to_timestamp to make it a timestamp. Finally use trunc to remove time part and make it a date.

user_64305230
Related