BUG::DFg2 TIME data type mishandling

[Element115]

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?

[Element115 · Accepted solution]

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.  

[miguel]

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?

[Element115 · Accepted solution]

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.  

[miguel · Accepted solution]

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