Hive trunc date format issues

user_2878290 0
Viewed 93

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

greenplum hiveql hive date sql
1 Answers

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))
user_27003440
Related