Support Questions
Find answers, ask questions, and share your expertise
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

Create new table with records between two dates within date range in HIVE

New Contributor
I am looking for discharge within 30 days of admission. The first part of the code runs where I use case statements and create the two variables exp and exp1 , but the second when I try to look for 30 days within admission date doesn't work. How do I make this run in HIVE?

I added an example of what I have and what I would expect to get in the new table

        SELECT *, 
        case when status in ('28' ,'44' ,'13' ) then 1 else 0 end as exp,
         case when status ='33' then 1 else 0 end as exp1 
            from old
         WHERE DATE_FORMAT (clinic_admit_dt, 'yyyyMM') >=  '201608' AND DATE_FORMAT(clinic_discharge_dt, 'yyyyMM') <= '201610' , 30)


HI....You can use datediff function like this :

WHERE datediff(clinic_discharge_dt,clinic_admit_dt) <= 30

Hope this helps. If the comment helps you to find a solution or move forward, please accept it as a solution for other community members.