CODEWITHSUNDEEP
sqlhadoophivehue
Aka
Aka

Reputation: 1

How to calculate birthdays in HIVE/HUE?

I am querying in hue/hive and want to get the list of customers who are turning 64 years + 30 in the current month or the next month. The database has the DOB in yyyy-mm-dd format. How can I achieve this? Please help thanks.

This is what I have so far

SELECT floor(datediff(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())),TO_DATE(BIRTH_DT))/365) AS age FROM table where (floor(datediff(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())),TO_DATE(BIRTH_DT))/365) >= 64)

Upvotes: 0

Views: 1028

Answers (1)

kashmoney
kashmoney

Reputation: 97

Here is what I would use:

  SELECT * FROM table_name WHERE FLOOR(DATEDIFF(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())), dob) / 365.25)>=64

Hope this helps.

Upvotes: 0

Related Questions