Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Datediff in HIVE with SQL support

avatar
Contributor

Hi all,

I have table in HIVE with insertDTM and id. My job is to find out those member whose last insertDTM is 30 days older than the present date. I am using Datediff UFD for that. My query is something like ..

select * from finaldata where datediff(current_date,select max(insertdtm) from finaldata group by memberid)>30;

But it is giving some error. Its looks like datediff does not support any SQL as param.

One more thing , can any one explain why this is not working ? when I am calling datediff with SQL , how HIVE handle this ? My assumption was that first it will execute the SQL in between (in my case which is "select max(insertdtm) from finaldata group by memberid") then call the Datediff with current_date and the output of this SQL. But it looks like it is not working that way.

Thanks a lot ...

1 ACCEPTED SOLUTION

avatar
Expert Contributor

Hi,

the problem is that your query is syntactically wrong. The right query to achieve your goal is:

select memberid, max(insertdtm) from finaldata group by memberid having datediff(current_date, max(insertdtm))>30;

Hope it helps.

View solution in original post

1 REPLY 1

avatar
Expert Contributor

Hi,

the problem is that your query is syntactically wrong. The right query to achieve your goal is:

select memberid, max(insertdtm) from finaldata group by memberid having datediff(current_date, max(insertdtm))>30;

Hope it helps.