Forum Discussion
Help With query
Hi,
I am trying to transform a utc date time column to a specific timezone by using DateTime.Add Zone but cant quite get the syantax right.
My code is
= Table.TransformColumnTypes(#"Filtered Rows",{{"utit_quotefinaliseddate", type DateTime.AddZone(["utit_quotefinaliseddate"],10)}}))Thank in advance for any assistance
Todd
4 Replies
DateTime.AddZone does not change the underlying time. It merely adds a TimeZone segment with the stated hours offset.
To change a UTC time to the corresponding time in a specific time zone, you need to add/subtract the number of hours.
If you also want to add a time zone specifier, that is a separate operation.
For example, to change UTC time to Eastern Daylight Time:
Table.TransformColumns(Source, {"Date", each _ + #duration(0,-4,0,0), type datetime}),To change UTC time to Eastern Daylight Time and also add a timezone identifier:
Table.TransformColumns(Source,{"Date", each DateTime.AddZone(_ + #duration(0,-4,0,0),-4), type datetimezone})Original UTC Time:
Change to EDT:
Change to EDT and add time zone specifier:
Thanks for your help ronrsnfld
Looks like you cant execute this type of query on a Direct Query.
If that is the case, would it be then safe to say that if your using a datasource of Dataverse with any column that has a date, you would need to always use Import during your Transform process?
What is best practice in this type of scenario?
I'm sorry. Those questions are outside my area or expertise.
