Snowflake filter Query giving empty results in Databricks, same query working in Snowflake.

[Skv]

I am trying to fetch filter data based on date format on a date column. Below is the query forming in Databricks. In Databricks giving empty results.

SELECT * FROM Test.TestSchema.Address where TO_DATE(TO_VARCHAR(MODIFIEDDATE,'YYYY-MM-DD')) = '2023-09-10'

This Query formed in Databricks taken same in snowflake which is running fine and getting the data.

May I know what is the issue here.

[Kaniz_Fatma]

Hi @Skv, The issue you're experiencing could be using uppercase’ YYYY-MM-DD’ in your date format.

In Spark 3.0 and above, which includes current versions of Databricks Runtime, the DateTimeFormatter doesn't recognize’ YYYY-MM-DD’ as a valid date pattern. Instead, using the lowercase’ yyyy-MM-dd’ would be best.

Therefore, your query should be:

sql
SELECT * FROM Test.TestSchema.Address where TO_DATE(TO_VARCHAR(MODIFIEDDATE,'yyyy-MM-dd')) = '2023-09-10'

This behaviour change is due to the migration from the combination of Julian and Gregorian calendars to the Proleptic Gregorian calendar in Spark 3.0 and above. This could be why your query works in Snowflake but not Databricks.

Sources:
- [Using datetime values in Spark 3.0 and above](https://kb.databricks.com/sql/using-datetime-values-in-spark-30-and-above)

[Skv]

Changed the format with case Sensitive, same issue.