Unable to read a table with invalid dates using SQL endpoint

[ChetnaC]

Hi There,
We have some source system which treat NULL values as '0001-01-01' and some dates are before '1900-01-01'. When we try to read such data written using lakehouse notebook via SQL endpoint, we are getting unable to run query error. Has anyone faced this issue and overcome it.

I have attached a sample notebook which reproduces the issue.

from pyspark.sql.functions import *
from pyspark.sql.types import DateType

spark.conf.set("spark.sql.parquet.int96RebaseModeInRead", "LEGACY")
spark.conf.set("spark.sql.parquet.int96RebaseModeInWrite", "LEGACY")
spark.conf.set("spark.sql.parquet.datetimeRebaseModeInRead", "LEGACY")
spark.conf.set("spark.sql.parquet.datetimeRebaseModeInWrite", "LEGACY")

new_df = spark.createDataFrame([('0065-04-01',),('0001-01-01',),('1993-03-11',)], ['COVER_END_DATE'])
new_df.select(col('COVER_END_DATE')).show()

spark.sql(f"CREATE TABLE IF NOT EXISTS test_date_issue2(COVER_END_DATE DATE) using DELTA LOCATION 'Tables/test_date_issue2'")

new_df.createOrReplaceTempView("TARGET_VIEW")

spark.sql(f"INSERT INTO test_date_issue2 SELECT * FROM TARGET_VIEW")

Thanks,

Chetna

[v-gchenna-msft]

Hi @ChetnaC ,

Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[v-gchenna-msft]

Hi @ChetnaC ,

Apologize for the issue you are facing. We can confirm that we are also observing the same issue at our end.

The best course of action is to open a support ticket and have our support team take a closer look at it.

Please reach out to our support team so they can do a more thorough investigation on why this it is happening: Link 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Hope this helps. Please let us know if you have any other queries.

[v-gchenna-msft]

Hi @ChetnaC ,

We haven’t heard from you on the last response and was just checking back to see if you got a chance to open a support ticket.
After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thanks