Forum Discussion
Convert column from Datetimezone to Datetime - M Query
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.
Please try this expression.
= DateTimeZone.RemoveZone([DTZ]) + #duration(0, DateTimeZone.ZoneHours([DTZ]),0,0)
Pat
6 Replies
Please try this expression.
= DateTimeZone.RemoveZone([DTZ]) + #duration(0, DateTimeZone.ZoneHours([DTZ]),0,0)
Pat
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
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.
Keshavpooja
You can add zero as the parameter SwitchZone:DateTimeZone.RemoveZone( DateTimeZone.SwitchZone([DateTime], 0))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.
