TIME Datatype issue while migrating from Synapse to FabricWarehouse

[AnmolGan81]

I am encountring an issue while migrating the data from synapse dedicated pool to fabric warehouse, one of my table contains a Time(2) dataype at source and destination, when we try to migrate it using Fabric Data Pipeline we get an issue related to "TIME is not compatible with external data type 'Parquet physical type: INT64, please try with BIGINT'", when I try convert it at source to something like varchar(20), I then get an issue of data conversion.

Does anyone faced any issue while performing migration whats the best option here to migrate time related data obejcts?

[v-mdharahman]

Hi @AnmolGan81,

Thanks for reaching out to the Microsoft fabric community forum and for sharing the details. Yes, this is a known friction point when working with time datatypes in Fabric pipelines, especially when moving data through Parquet-based internals. To resolve this, here's what usually works:

* Convert the TIME(2) column to VARCHAR(8) or VARCHAR(12) at the source before pushing through the pipeline. Use a consistent format like 'HH:MM:SS' or 'HH:MM:SS.FF' depending on your precision needs. For example, in your SELECT query from Synapse "CONVERT(VARCHAR(12), your_time_column, 114) AS formatted_time". This format (114) gives time in hh:mi:ss:mmm which avoids conversion issues.

* At the destination (Fabric Warehouse), either keep it as VARCHAR if the time is used for display/logging, or convert it back to TIME(2) using a computed column or post-processing step.

* If your downstream model needs the actual time type, consider landing the data as VARCHAR first, then running a SQL statement in Fabric to transform it back.

ALTER TABLE your_table
ADD new_time_column AS CAST(formatted_time AS TIME(2))

This approach keeps the pipeline flowing without breaking due to type mismatch, and allows flexibility to map it back to the intended type at the destination.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[AnmolGan81]

Hi thank you for your response I have tried below:

Convert the TIME(2) column to VARCHAR(8) or VARCHAR(12) at the source before pushing through the pipeline. Use a consistent format like 'HH:MM:SS' or 'HH:MM:SS.FF' depending on your precision needs. For example, in your SELECT query from Synapse "CONVERT(VARCHAR(12), your_time_column, 114) AS formatted_time". This format (114) gives time in hh:mi:ss:mmm which avoids conversion issues.

So I tried above in copy activity itself at source side, but its not able to convert I get the same error as I mentioned before, can you elaborate more on your answer ?