I am trying to convert GP to Hive migration logic but below statement giving the wrong output while execute query:
select (date_trunc('Month','2022-01-04'::date ) - interval '0 Month + 1 day')::date
output for GP : 2021-12-31
similar if we converted Hive query
select cast(date_add(add_months(trunc(cast('2022-01-04' as date),'MM'),-0),1) as date)
out put if Hive query : 2022-01-02
i could see the difference of the date. please help me.
Thanks
You are subtracting interval 0 months and 1 day in the first query, in second query you are adding 0 months (it makes no sense) and adding 1 day (not subtracting). If you want to do the same as in GP query in Hive just subtract 1 day, it will work the same:
select date(date_add(trunc('2022-01-04','MM') ,-1)) --returns 2021-12-31
Or the same using interval:
select date(date(trunc('2022-01-04','MM')) - interval '1' day)
Or one more possible alternative:
select date(add_months(last_day('2022-01-04') ,-1))