converting text to datetime field sometimes change to previous date due to timezone

[jasonyeung87]

Hi,

I have a column in my table that is a "text" field. The value of the field is a datetime in the format of "2022-11-09T00:00:00Z". I convert this field to a date time field (e.g. in Power query editor, I right click the column header, then go to Change Type --> Date/Time):

What's happening is that the resulting date would be the previous date, depending on the timezone. I'm in the Pacific time zone and text like "2022-11-09T00:00:00Z" is converted to 2022-11-08T16:00:00Z" because Pacific is 8 hours behind GMT. 

I was wondering how do I convert to a datetime field while ignoring the time in the text.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1NNQ1sAwxMLACoyil2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column1],each DateTimeZone.RemoveZone([Column1]),Replacer.ReplaceValue,{"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type date}})
in
    #"Changed Type1"

[lbendlin]

Since it is in ISO8601 format you should first convert it to DateTimeZone and then in a separate step convert that to Date.

[pi_eye]

Hi Jason!

I was able to recreate this issue by changing my timezone on my laptop. So this is an option - if you change your local desktop to UTC, but if that is not practical, you can also try converting to date/time/timezone 

Let me know if it works!

Pi

[jasonyeung87]

Hi @pi_eye ,

Is there a way to keep the date the same? For example if the text is "2022-11-09T00:00:00Z", I want the resulting date to be "2022-11-09" instead of "2022-11-08". I don't want it to take the timezone into account when it's converted to the date.

Jason

[pi_eye · Accepted solution]

Hi @jasonyeung87 !

So I changed my timezone on my PC to -12 hours, and this is what I am getting

(excel on the left, PBI on the right)

If I right click, change type to date/time/timezone it seems to fix the problem.

to recreate, make sure that you select  "replace current" step

This seemed to fix the issue

There are functions that can help -  see  https://learn.microsoft.com/en-us/powerquery-m/datetimezone-functions

You will need to change the type to date/time/timezone before

using these.

EG: 

^ this will change the time zone by -5 hours

You could also try DateTimeZone.ToLocal() 

HTH 

Pi

[jasonyeung87]

Hi @pi_eye ,

thanks for your help. I converted the type to "date/time/timezone" and then looked at what functions are available for the "date/time/timezone" type.

I found a function named "DateTimeZone.RemoveZone". I created a new column with this function (e.g. "DateTimeZone.RemoveZone(thefield)") and it worked for me.

Jason

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1NNQ1sAwxMLACoyil2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column1],each DateTimeZone.RemoveZone([Column1]),Replacer.ReplaceValue,{"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type date}})
in
    #"Changed Type1"

[jasonyeung87]

Hi @lbendlin ,

thanks for your help. converting the field to "date/time/timezone" and then using the DateTimeZone.RemoveZone function did the trick.

Jason