Forum Discussion

h4tt3n's avatar
h4tt3n
Icon for Helper V rankHelper V
6 years ago

Convert a columns of datatype datetimezone to datetime

Hello,   I have a table with timestamped data of datatype datetimezone, eg. 07-08-2020 08:00:00 + 02:00.  How do I convert this to datetime in a way that takes tz info into account, eg. 07-08-2020...
Need Help
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

EDIT: I've just noticed that this is almost 5 years old topic, but maybe it will help to someone...

Hi h4tt3n, check this:

 

 

let
    Source = #table(null, {{"07-08-2020 08:00:00 +02:00"}}),
    Ad_CorrectDateTime = Table.AddColumn(Source, "CorrectDateTime", each let a = DateTimeZone.From([Column1]) in DateTime.From(a) + #duration(0, DateTimeZone.ZoneHours(a), DateTimeZone.ZoneMinutes(a), 0), type datetime)
in
    Ad_CorrectDateTime

 

 

If there is a space after + sign (as you showed us in your sample) use this:

 

let
    Source = #table(null, {{"07-08-2020 08:00:00 + 02:00"}}),
    Ad_CorrectDateTime = Table.AddColumn(Source, "CorrectDateTime", each let a = DateTimeZone.From(Text.Combine(Text.Split([Column1], " + "), " +")) in DateTime.From(a) + #duration(0, DateTimeZone.ZoneHours(a), DateTimeZone.ZoneMinutes(a), 0), type datetime)
in
    Ad_CorrectDateTime