Hive + Pass previous day date to like clause

Question

Am trying to fetch records from hive table based on the previous date. Example: If table is as follows

CustomerVisit table

ID NAME VISIT_DATE
------------------

01 Harish 2018-02-31

03 Satish 2017-02-13

04 Shiva  2018-03-04

Now i need to fetch all records that have visit_date = 2018-03-04 (i.e today's date -1).

Expected Query something like:

select ID, Name from CustomerVisit where 
visit_date like concat((select date_sub(current_date, 1)),'%')

I have tried following

select current_date; - Gives current date

select date_sub(current_date, 1) - Gives previous date

select concat(tab1.date1,'%') from 
(select date_sub(current_date, 1) as date1) as tab1; -- Gives the previous date appended with % which i need to use in like

but when i use the above as sub-query like below it fails with

select tab2.id, (select concat(tab1.date1,'%') as pattern from 
(select date_sub(current_date, 1) as date1) as tab1) from CustomerVisit as tab2 limit 1;

FAILED: ParseException line 1:0 cannot recognize input near 'seelct' 'current_date' ''

How to write query to get results for previous date?

Answer 1

You don't need a LIKE clause. Just select using an equal to (=)

select ID, Name from CustomerVisit where 
visit_date = date_sub(current_date, 1);