topic Re: Date field in lakehouse table becomes datetime field in Dataflow

Date field in lakehouse table becomes datetime field

ebjim — Tue, 12 Sep 2023 19:28:39 GMT

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?

Re: Date field in lakehouse table becomes datetime field

Pragati11 — Tue, 19 Sep 2023 12:45:59 GMT

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.

Re: Date field in lakehouse table becomes datetime field

miguel — Tue, 19 Sep 2023 13:15:05 GMT

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

Re: Date field in lakehouse table becomes datetime field

Pragati11 — Tue, 19 Sep 2023 13:16:47 GMT

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.

Re: Date field in lakehouse table becomes datetime field

ebjim — Tue, 19 Sep 2023 13:23:05 GMT

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.

Re: Date field in lakehouse table becomes datetime field

ebjim — Tue, 19 Sep 2023 13:25:03 GMT

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.

Re: Date field in lakehouse table becomes datetime field

Pragati11 — Tue, 19 Sep 2023 13:26:51 GMT

Hi ebjim

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

Re: Date field in lakehouse table becomes datetime field

ebjim — Mon, 25 Sep 2023 16:47:08 GMT

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

Re: Date field in lakehouse table becomes datetime field

pbernardes — Sat, 28 Oct 2023 21:04:38 GMT

Hi ebjim !

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

Re: Date field in lakehouse table becomes datetime field

miguel — Sat, 28 Oct 2023 22:03:26 GMT

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

Re: Date field in lakehouse table becomes datetime field

Anonymous — Tue, 31 Oct 2023 18:09:29 GMT

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.

Re: Date field in lakehouse table becomes datetime field

Pragati11 — Wed, 01 Nov 2023 09:03:20 GMT

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. 🙂