CODEWITHSUNDEEP
sqlhivehiveql
DevEx
DevEx

Reputation: 4571

HiveQL select birthdays in a specific date range

I have the following table of persons

id name   location dob
1  John   NY       1978-08-02
2  Nancy  LA       1993-10-12
3  Smith  GA       1986-09-25
4  Emily  MN       1988-04-14
5  Lily   MN       1978-11-02

How to write HiveQL to select Birthdays between current date and 2 months back:

For example, 

select current_date
>> 2018-09-27

select add_months(current_date,-2);
>> 2018-07-27

select birthdays between those dates

id name   location dob
1  John   NY       1978-08-02
3  Smith  GA       1986-09-25

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270341

Birthday problems are tricky:

select t.*
from t
where (year(current_date) = year(add_months(current_date, -2)) and
       date_format(dob, 'MM-DD') between date_format(add_months(current_date, -2), 'MM-DD') and date_format(current_date, 'MM-DD')
      ) or
      (year(current_date) > year(add_months(current_date, -2)) and
       date_format(dob, 'MM-DD') not between date_format(current_date, 'MM-DD') and date_format(add_months(current_date, -2), 'MM-DD')
      )

Basically, you have to do the comparisons for the month and day only, not the year. Then, you have to take into account when the period goes over a year boundary.

Upvotes: 1

Related Questions