Is this normal? Date column shows as datetime in Fabric UI

[arlindTrystar]

Is this expected?

Screenshot from Fabric Lakehouse UI

Same data from Fabric PySpark Notebook:

And schema:

Why is this?

[lbendlin · Accepted solution]

There is no such thing as a Date data type.  Everything is always DateTime.  When you ask for "Date"  Power BI and Fabric will give you a DateTime value pegged at midnight UTC.

[frithjof_v]

Interesting. Could you reference to some docs which explain this?

I still think it's a bad user experience to show the 12:00:00 AM in each cell when the data type is set to Date in the delta table. 

It adds cognitive load to the user.

[lbendlin]

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types

Wait until you learn about Int128 ...

[frithjof_v]

Nice, thanks for sharing!

I'm curious to learn about the Int128 case!

However, the OP is showing the Lakehouse explorer, not a Power BI semantic model. So I'm not sure if the Power BI documentation is relevant for this case.

And OP has shown that the data type is date (OrderDate: date (nullable = true)).

So it seems that the Lakehouse explorer is formatting date type as date/time? 

[frithjof_v]

I did a similar test, and got the same results as the OP:

Notebook user interface:

Lakehouse user interface:

[lbendlin]

They use the same/similar engine, so they are constrained to the same data types.

Wait until you learn about how DateTimeZone is represented 🙂

[frithjof_v]

"They use the same/similar engine, so they are constrained to the same data types."

Is there some documentation or information regarding this somewhere? It would be interesting to learn more about it.

Could you explain the DateTimeZone data type? The way I understand it, it gets converted to plain DateTime when it loads from Power Query into the Power BI semantic model. Does it basically just ignore the timezone component when loading from Power Query into Power BI?

About date. It is listed in the data type dropdown in Power BI.

https://learn.microsoft.com/en-us/power-bi/connect-data/media/desktop-data-types/pbiddatatypesindata...

Is this data type dropdown really just a formatting option, not an actual data type option?

I guess this video touches upon the same issue: https://youtu.be/SmczqYv_rfY?si=QuHnhBJzb4LIridk

And this article also: Power BI’s Pseudo-Time & Pseudo-Date Types | Ben Gribaudo

Does it depend on which data types we switch between? So choosing text in the data type dropdown would actually change the data type, whereas choosing date would just change the formatting of the datetime value?