Convert column from Datetimezone to Datetime - M Query

[Keshavpooja]

Hi Experts,

I am getting values from the source like '2021-02-10 20:48:24 +01:00' which has been assigned type 'DateTimeZone' in the Query Editor. I would like to store it as 'DateTime' format with the value as '2021-02-10 21:48:24'. I have tried few steps which didn't work. Could you pls help with the correct M query.

1. First I tried to assign it directly as DateTime type assuming that would work in the way I expected, but no luck.

2. Then I tried --> DateTimeZone.RemoveZone(DateTimeZone.SwitchZone([#"Date/Time Opened"],1))  -->  but didn't work either.

Thanks & Regards,

Kesavan.

[mahoneypat · Accepted solution]

Please try this expression.

= DateTimeZone.RemoveZone([DTZ]) + #duration(0, DateTimeZone.ZoneHours([DTZ]),0,0)

Pat

[Keshavpooja]

Hi @mahoneypat,

That's superb. It works. Thanks a lot.

//Kesavan.

[Fowmy]

@Keshavpooja 

You can add zero as the parameter SwitchZone:

DateTimeZone.RemoveZone( DateTimeZone.SwitchZone([DateTime], 0))

[Keshavpooja]

Hi @Fowmy 

If I add 0 as parameter as you suggested, it becomes '2021-02-10 19:48:24'. 

Something seems to be wrong here. It behaves wierd.

[StefanoGrimaldi]

simple change the column to data/time/timezone first, them change it back to date/time or date/time using locale, so that should work and give you the date/time in the format you want 

[Keshavpooja]

Hi,

It does not work. Pls see the time closely. If i do like you said  i get only 20:48:24 but i would need 21:48:24

Kesavan.