Filter data by Date using where condition (< TargetDate) giving "Query returned no results"

[Anonymous]

Code is working good if data greater than target date (>) is selected :

SELECT        

xyz.ID,

xyz.Gender,

xyz.geography,

xyz.code,

xyz.delivery_status,

abc.department_code

FROM v.table1 as xyz

left join y.table2 as abc

on

xyz.ID = abc.ID AND 

xyz.code = abc.code

where xyz.partition_date < '2021-01-01' 

group by 1,2,3,4,5,6

-- It's working when using xyz.partition_date >= 2020-01-31 

@Aman Sehgal​  @Stephan Lawson​  @Darryll Petrancuri​  @sql-thetan​ 

[artsheiko]

The fact that you get records for >= 2020-01-31 doesn't mean you should get something for < 2021-01-01 as well.

Let's say the set contains the date 2099-01-01. Then, when applying the first condition, you will see the corresponding row, but applying the second one you will get "Query returned no results" which makes sense.

[Anonymous]

Hi @Artem Sheiko​ 

Thanks for your answer. The problem is data is available from 2017-01-01 to current date. And if I search for records > 2017-01-01 it will work but if I do same for < Any Date (Let' say 2022-01-01) It will give no results. "Between" any date range is also not working.

[AmanSehgal]

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

[Vidula]

Hi @Rishabh Shankar​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!