topic Re: Date field missmatch in Developer

Date field missmatch

Anonymous — Thu, 25 Mar 2021 14:30:15 GMT

Currently, when dates are converted to ISO dates, we add a time component too (midnight).

eg. 01/11/2014 -> '2014-11-01T00:00:00.000Z'

This is fine for dates fall in winter (before clock change). However, dates fall in the summer (BST), conversion off set 1 hour making the midnight of a day to the 11PM on previous day.

eg. 01/04/2014 -> '2014-03-31T23:00:00.000Z'

Re: Date field missmatch

lbendlin — Sun, 28 Mar 2021 16:46:38 GMT

The Z here means ZULU, or UTC. If you use UTC timestamps throughout your data then that is not impacted by DST changes.

In ISO8601 you can also specify the UTC offset as needed.

Re: Date field missmatch

V-lianl-msft — Tue, 30 Mar 2021 05:40:42 GMT

Hi Anonymous ,

Please first convert the data type of these columns to Date/Time in Query Editor.

You can refer to these thread:

https://community.powerbi.com/t5/Desktop/Support-for-ISO-8601-Date-Time-format-DateTime-ToString-quot-O/m-p/309187

https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-savings/m-p/789919

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.