Hive-Query to get data from 10th of current month to current date if sysdate is greater than 10th else query from 10th of previous month to sysdate

user_11459770 0
Viewed 178

I have a requirement in which I need to query the dataset from current date to previous 10th of the month. Example-If the date is 27th Jan today then the query would consider the data from 10th Jan to 27th Jan.

In case the current date is less than 10th of this month then the query should consider the data from 10th of last month to current date. Example-If the date is 3rd Jan today than the query should consider the data from 10th Dec to 3rd Jan.

I have tried the below query to get the results for 1st scenario.

select * from my_table
where partition_col >= date_add(current_date,1 - day(current_date)+9)
and partition_col <= current_date;

Could anyone help to also manage the first scenario as well in a single query.

hiveql hive date sql
1 Answers

Use CASE expressions like this:

where partition_col >= case when day(current_date)>=10 
                            then date(concat(substr(current_date,1,7), '-10'))
                            else date(concat(substr(add_months(current_date,-1),1,7),'-10'))
                       end
     and partition_col <= current_date;
user_27003440
Related