topic Re: Filter data by Date using where condition (< TargetDate) giving "Query returned no results" in Data Engineering

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

Anonymous — Fri, 05 Aug 2022 17:17:50 GMT

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

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

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

artsheiko — Sun, 07 Aug 2022 13:26:21 GMT

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.

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

Anonymous — Sun, 07 Aug 2022 18:09:42 GMT

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.

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

AmanSehgal — Mon, 08 Aug 2022 04:56:25 GMT

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

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

Vidula — Wed, 07 Sep 2022 12:15:38 GMT

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!