How to get previous month from 'yyyy-MM' date format in Hive

Question

I want to get previous month from 'yyyy-MM' format value in Hive. I tried with 'add_months' but couldn't able to get.

select add_months('2021-06', -1)

Eg: I have one string column having value '2021-06', now I want to display output as '2021-05'.

Please help on this.

Answer 1

Convert your string to date by concatenating with '-01', add_months, use date_format to get yyyy-MM:

select date_format(add_months(concat('2021-06','-01'), -1),'yyyy-MM')

Result:

2021-05

Another method (using substr instead of date_format):

select substr(add_months(concat('2021-06','-01'), -1),1,7) --returns 2021-05