how to converting string date in 'yyyy-m-dd' to 'yyyy-mm-dd' in Hive query?

user_3486773 0
Viewed 169

I searched up and down but couldn't find anything that works.

I have a date that is stored as a string in this format: '2021-9-01' so there are no leading zeros in the month column. This is an issue when trying to select a max date as it interprets September to be greater than October.

Any time I run something that tried to convert this it literally never finishes. I can pull back 1 row when selecting * from... but this fails to complete:

  select unix_timestamp(bad_date, 'yyyy-m-dd') from mytable

I'm using hive query so not sure how to make this conversion work so I can actually get October (this month) to show up as the max date?

hive unix-timestamp timestamp date hql
1 Answers

Correct pattern for month is MM. mm is minutes.

from_unixtime(unix_timestamp(bad_date, 'yyyy-M-dd'),'yyyy-MM-dd')

One more method is to split and concatenate with lpad:

select concat_ws('-',splitted[0], lpad(splitted[1],2,0),splitted[2])
from
(
select split('2021-9-01','-') splitted
)s

Result:

2021-09-01
user_27003440
Related