Datediff in HIVE with SQL support

[#1]

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 ...

[#2 · Accepted solution]

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.