topic Re: BUG::DFg2 TIME data type mishandling in Dataflow

BUG::DFg2 TIME data type mishandling

Element115 — Thu, 15 Feb 2024 07:18:19 GMT

STEPS

0__create first DFg2 and ingest a table from on-prem SQL Server DB that has a datetime2 column; do not set a data destination; Enable staging.

1__create a second DFg2 and set the previous DFg2 as source.

2__split the datetime column into 2, namely one date column and one time column (already something is not right: the incoming datetime separated date and time with a ',' (comma) followed by a space character, but the source datetime separator is space only).

3__change the data type of the time column to type time.

4__set the data destination to a lakehouse you created before all this, select new table in replace mode and you will see the UI complain that data type time for column time is not supported.

5__very well, in the UI you change the type to datetime.

6__you are now back inside Power Query or DFg2 UI and the date and time columns now look like this:

So typeTIME needs to be supported, and the separator between date and time should be space only, not comma-space.

Can the product team fix this?

Re: BUG::DFg2 TIME data type mishandling

miguel — Thu, 15 Feb 2024 05:52:08 GMT

Hey!

I'm not sure I follow. Is the issue that you're reporting around how a data type transformation occurs from a time data type to a datetime data type?

Or is the issue that you're reporting the lack of support for the time data type at the Lakehouse?

Re: BUG::DFg2 TIME data type mishandling

Element115 — Thu, 15 Feb 2024 07:16:32 GMT

The latter. Every single time I have a column of type TIME, when I select lakehouse as the destination, it forces me to change the data type to DATETIME because TIME is not supported. Once it brings you back to the Power Query UI (see screenshot) the TIME column, now a DATETIME type, shows the separator between date and time as ', ' that is to say a comma followed by a space, instead of just space as in SQL Server.

Ideally, the type TIME should be supported by a lakehouse, just like in a DB.

Oh I see my mistake... I was getting so upset I miswrote... of course there will be a date in the time column since it is now of type datetime. Duh. My bad.

Re: BUG::DFg2 TIME data type mishandling

miguel — Thu, 15 Feb 2024 14:07:01 GMT

This is currently by-design. The Delta format does not support a time type. Fundamentally, a limitation of the destination and not Dataflow Gen2.

You can suggest the idea to support a time data type for the Lakehouse using the link below:

https://aka.ms/FabricIdeas