thank you for your input.

I tried to select the column by itself from the table as you showed and yes, I get the format converted right from yyyyMMdd to yyyy-MM-dd on output, but when I put it in the where clause as below:

select count(*) from table_name where to_date(cast(column_name as string),'yyyyMMdd') = date_sub(current_date(),1));

my output is 0 instead of more than 500000 records.