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

Question

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.

Answer 1

Hi @Anonymous

In Delta Lake Tables the only accepted formats are:

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

Example formats:

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.