cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Anonymous
Not applicable

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​ 

4 REPLIES 4

artsheiko
Honored Contributor

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
Not applicable

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
Honored Contributor III

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
Honored Contributor

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!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group