Trouble with a where clause which looks back 24 months from current date

Question

I have this where clause which works fine in Impala but is throwing an error in Hive. What I'm trying to do is only bring in data 24 months prior to the current day.

select * from my.database
where period_date > add_months(now(), -24);

Just erroring out and not producing any results.

Answer 1

Unlike Impala, Hive does not have a now() function. It complies with ANSI SQL by providing current_date and current_timestamp registers (starting with version 1.2 [ref]). So for Hive, this should work:

select * from my.database
where period_date > add_months(current_date, -24);