How to store date with year before 1900 in synapse delta lake table ?

heta desai 247 Reputation points

I need to pull data from SQL Server and load into synapse delta lake table. In SQL Server table there is datetime column in which there is date with year before 1900. When I tried to load this data into synapse delta lake it throws and error that year can not be before 1900.

Please suggest me the way to store datetime value with year before 1900 in synapse delta lake table.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,671 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,071 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee

    Hi @Anonymous ,

    Thanks for posting query in Microsoft Q&A Platform.

    Kindly try to execute below code on Spark pool to make sure before 1900 dates also allowed on Spark.

    spark.conf.set("spark.sql.legacy.parquet.int96RebaseModeInWrite", "CORRECTED")  

    Please let us know how it goes.

    Hope this helps. Please let me know if any further queries.


    Please consider hitting Accept Answer button. Accepted answers help community as well.

    1 person found this answer helpful.