Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Datediff in HIVE with SQL support

Solved Go to solution

Datediff in HIVE with SQL support

New 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

Accepted Solutions

Re: Datediff in HIVE with SQL support

Rising Star

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.

1 REPLY 1

Re: Datediff in HIVE with SQL support

Rising Star

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.

Don't have an account?
Coming from Hortonworks? Activate your account here