Date field in lakehouse table becomes datetime field

[ebjim]

I have a dataflow where it creates a new lakehouse table as the data destination. There are date fields in both source and destination:

The dataflows refreshes and load data. However, the lakehouse table actually has datetime fields, instead of date fields:

I made sure my data was correct and I have seen anything out there that tells me why or how to address the issue. Has anyone else come across this?

[ebjim]

I was able to replicate the issue for the Support team. Awaiting for their response.

[miguel]

hey @ebjim ! are yous till seeing this issue today? any chance that you could share some repro steps?

[ebjim]

Hi @miguel , I actually had a call with a support rep yesterday and did a screen share with him, showing him the same steps as what @Pragati11 just posted. Trouble is, the dataflow refresh went into an 8 hour death spiral. But I am sure that issue persists.

[pbernardes]

Hi @ebjim !

I just faced same issue. Did you find any solution?

[miguel]

I'd recommend reaching out to the support team so they can help troubleshoot the scenario and provide solutions. 

[Pragati11]

HI @miguel,

I can confirm that this issue is there today as based on my previous response I did a test 2 hours back and I still see all date columns are written with DATETIME datatype in the Lakehouse tables.

[Pragati11]

HI @ebjim ,

Based on my understaning, Lakehouse only supports Datetime data-type therefore you see datetime data-type for your columns in the lakehouse tables.

I did a test on my side just to confirm my theory above.

This is the FACT SALES table which I am getting through my Dataflow Gen 2 into a Lakehouse:

I choose Lakehouse as my destination:

Then on the destination table, by default DATETIME is datatype:

Then I make sure I modify it to DATE datatype:

After saving the settings and publishing the dataflow Gen2, I see that within the lakehouse, the columns is still written as with DATETIME datatype:

This shows my understanding about Lakehouse supporting only DATETIME data type for Date columns is right.

I would still like to see if @miguel has anything to add here.

[ebjim]

@Pragati11 This is what I have experienced. The strange thing is that I had run the same steps last month and got date type columns. This issue caught me by surprise.

[Pragati11]

Hi @ebjim 

I still think Lakehouse supports DATETIME only but let's see and wait for a response from @miguel 🙂

[Anonymous]

Hi Prgati,

Lakehouse supports both DATETIME (timestamp) and Date. 
I now have two tables uploaded to Lakehouse, where I tried to convert the date to type 'date'. However, one of it keeps 'date' but the other is stored as 'datetime' (timestamp) in lakehouse.

Then in the Warehouse (SQL End point), the date keeps date, and the timestamp becomes datetime2.

[Pragati11]

Hi @Anonymous ,

I think the issue was when writing Lakehouse tables via Dataflow Gen2 but not about updating datatypes directly in Lakehouse tables.

If you see the detailed comment of mine at the top, you will know what issue was raised.

But I haven't checked this in the last few weeks incase if it is fixed. 🙂