CODEWITHSUNDEEP
sqlhivehiveql
Reema
Reema

Reputation: 33

Hive query to get a date within a month range of another date

I have a query as below:

select product_id, approved_date, event_date from table1

I have to make sure to pull the product_ids which have event_date within 1 month period of approved_date.

for example, if event_date is 2020-04-11, the approved date should be between 2020-03-11 and 2020-04-10 Could someone please help with this query?

Upvotes: 1

Views: 384

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would use direct comparison:

where event_date >= add_months(approved_date, -1) and
      event_date < add_months(approved_date, 1)

Upvotes: 1

Annosz
Annosz

Reputation: 1032

You can use MONTHS_BETWEEN to check for difference between two dates:

SELECT product_id, approved_date, event_date
FROM table1
WHERE ABS(CAST(MONTHS_BETWEEN(event_date, approved_date) AS INT)) <=1

Just make sure to use ABS to include approved_date before and after the event_date

Upvotes: 1

Related Questions