Datediff in HIVE with SQL support

Biswajit16 — Wed, 14 Jun 2017 17:51:10 GMT

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

Re: Datediff in HIVE with SQL support

mgaido1 — Wed, 14 Jun 2017 22:38:41 GMT

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.

question Re: Datediff in HIVE with SQL support in Archives of Support Questions (Read Only)

Datediff in HIVE with SQL support

Re: Datediff in HIVE with SQL support