Support Questions
Find answers, ask questions, and share your expertise

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

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

CREATE TABLE  new AS 
        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)
);sample.PNG
1 REPLY 1

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

Contributor

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.