Which is the equivalent datatype of SQL Server Timestamp datatype in Delta lake table

Heta Desai 216 Reputation points
2022-08-16T14:28:09.197+00:00

I need to pull data from SQL server and write into Delta lake table using synapse pipeline. For timestamp column in SQL server I have tried using string datatype in target delta lake table but it stores junk characters. I have create synapse mapping dataflow to write data into target.

Here is the example of value stored in timestamp column in SQL Server : 0x000000002BAED0C9

Please suggest me the right datatype to use for SQL server timestamp in 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.
2,884 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dillon Silzer 36,546 Reputation points
    2022-08-17T18:39:39.433+00:00

    Hi @Heta Desai

    In Delta Lake Tables the only accepted formats are:

    232028-image.png

    https://docs.databricks.com/sql/language-manual/data-types/timestamp-type.html

    Example formats:

    232027-image.png

    https://learn.microsoft.com/en-us/azure/databricks/delta/quick-start#sql-6

    You will need to convert your hexadecimal format to the proper structure as seen above.

    ----------------------------

    If this is helpful please accept answer.