datetime2 shows timestamp

[smpa01]

I have ingested data with df gen2 cicd in lakehouse., one of the columns is datetime2 and shows correct value in sql endpoint

But shows strange value in lakehouse

Is there reason for this? How can I have the lakehouse dispay the same datetime2 value as it is displayed by sql endpoint?

@miguel 

[v-tsaipranay · Accepted solution]

Hi @smpa01 ,

Thanks for reaching out to the Microsoft Fabric Community.

Since you're using Dataflow Gen2 to ingest data into the Lakehouse via CI/CD and don’t have access to the Spark runtime, options like setting spark.sql.session.timeZone or using Spark-based formatting aren’t available in your case.

The difference you're seeing between the Lakehouse preview UI and the SQL Endpoint comes down to how each one handles timestamps. The SQL Endpoint shows full UTC precision (datetime2), while the Lakehouse UI might only show the time portion or adjust it based on your local timezone settings this depends on your client environment.

To keep things consistent without relying on Spark, you can use a Derived Column in your Dataflow Gen2 to format the timestamp as a string like this:

formatDateTime([timestamp_column], 'yyyy-MM-dd HH:mm:ss')

This approach ensures the full datetime is preserved during ingestion and avoids any display inconsistencies in the UI.

If you need precise values for validation or downstream processes, querying through the SQL Endpoint is your best.

Lastly, as the Lakehouse UI formatting behavior is by design, feel free to submit or upvote an idea here: https://ideas.fabric.microsoft.com

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[v-tsaipranay]

Hi @smpa01  ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[nilendraFabric]

Hi @smpa01 

it looks like The Lakehouse is likely converting the UTC time from the SQL Endpoint to a different timezone (e.g., `IST` or another local timezone).

Set the Lakehouse Spark session to use UTC to match the SQL

spark.conf.set("spark.sql.session.timeZone", "UTC")

Use explicit formatting to show both date and time with fractional seconds:

df.select(
date_format("timestamp_column", "yyyy-MM-dd HH:mm:ss.SSSSSS").alias("formatted_ts")
).show(truncate=False)

[smpa01]

I am ingesting data in the lakehouse using dataflow gen2 cicd, I have no way to play with spark config

[smpa01]

I keep on getting the same values in

Lakehouse

vs sql endpoint