Workflow table dates getting converted incorrectly on publishing to lakehouse

[jarmesb]

I have tried everything i can think of to fix this...

I have a CSV file that i have imported into lakehouse. This has start_date and end_date columns. After the import all the data is looking correct. I then have a workflow that transforms the rows so it expands the data out and you get one row for each month between start_date and end_date. When looking in the preview of this data, everything is looking fine. Date columns are set to date column types and the data is looking how i would expect it to. I select the Data Destination to be lakehouse and a new table. i have check the column data types and made sure they are all dates. When i publish the years are all getting messed up. I am getting the years coming through as 0202. I have tried changing all the dates to date times and moved the fields to be a text format of yyyy-MM-dd hh:mm:ss. If i export the data into text format then these fields come through correctly. So it must be something that lakehouse is doing. I cannot see any way i can control how it is tranforming these dates either....

I have looked to see if there is any locale configuration but cannot see anything obvious. Anyone any ideas?

[rajendraongole1]

Hi @jarmesb - This issue with dates getting formatted incorrectly upon publishing to the Lakehouse could stem from how the Lakehouse processes or stores dates. Here are some troubleshooting steps and solutions that might help:

Check for Inconsistencies in Date Types:

Even if the preview shows dates as expected, inconsistencies may arise in how dates are handled internally by the Lakehouse storage engine.
Before finalizing the workflow, ensure that dates are explicitly cast to a consistent format (e.g., DATE or DATETIME) across all transformation steps.
Try Using an Explicit Date Conversion Function:

In some systems, using functions like FORMAT() or CONVERT() to explicitly set the format can force the Lakehouse engine to interpret dates correctly. If you can, try casting or formatting dates right before writing them to the Lakehouse.
Investigate Locale/Regional Settings on the Data Source and Destination:

Even if there isn’t a direct setting for locale in the Lakehouse UI, it’s worth checking the locale settings in your environment (Power BI, Lakehouse configuration, or underlying SQL database settings if applicable).
Sometimes, the environment’s default locale setting can influence how dates are interpreted.
Test with Alternate Date Storage Formats:

Since you mentioned that exporting as text format works correctly, you might try storing dates as text (yyyy-MM-dd) instead of actual DATE or DATETIME types in the Lakehouse.
Once the data is in Lakehouse, you could add a calculated column that converts the text date back to a date format if needed.
Use a Dummy Date Table for Validation:

To confirm if the issue lies within the Lakehouse storage, try uploading a small dummy CSV with only date values in different formats to the Lakehouse.
If these dates transform correctly, the issue may be specific to how the Lakehouse processes data from your workflow.
Inspect Transformation Logic on Re-import:

After publishing, re-import the table from Lakehouse back into Power BI or another visualization tool. This can help identify if the issue is with Lakehouse’s storage or the process that publishes to it.
If none of these steps work, it may be beneficial to reach out to Lakehouse’s support or documentation to confirm if there are known limitations or specific steps for handling dates during data transformation and storage.