When comparing date fields, results can sometimes be erroneous.
From what your explanation above, I can recommend following for you:
- Check number of rows in each date partition. It looks like all your data from 2017-01-01 could be sitting in one partition and hence you're getting no results for less than and between query but you're getting results for greater than query.
select partition_date, count(*) from v.table1 group by partition_date having count(*)>0
- Use to_date in your query for comparing dates.
select to_date(partition_date, 'yyyy-MM-dd') as partition_dt from v.table1