Subtract 3 months to yyyymm month format column hive sql

Question

In HiveSql I have a yearmonth [yyyymm] column from which I need to subtract 3 months For example: if yearmonth is 201912 , the record required is 201909

Can someone please help me with the syntax or script I need to get for this ?

I have tried addmonths, conv(), and reg_extract But nothing works

Answer 1

add_months() function works with dates. Convert yyyyMM to yyyy-MM-01 date, apply add_months and format as yyyyMM again:

with your_table as (select '201912' as yearmonth)

select date_format(add_months(concat_ws('-',substr(yearmonth,1,4),substr(yearmonth,5,2),'01'),-3),'yyyyMM') as yearmonth 
  from your_table;

Result:

201909