Forum Discussion

mr_fnord's avatar
mr_fnord
Frequent Visitor
5 years ago

Local Datetime to UTC, inconsistent behavior from DateTimeZone.From()

I have a datatable from a SQL database with a local datetime column [DeliveryDateTime] that I need to join to other tables with UTC times. 

 

I am using a Custom Column with the formula:

DateTimeZoneLocal = DateTimeZone.From([DeliveryDateTime])

 

When I add this step, I get a result that treats [DeliveryDateTime] as a UTC time:

which is not what the documentation describes.

 

If I add another Custom column step, either before or after the DateTimeZone.From step, suddenly the DateTimeZone.From starts assigning the local timezone.

 

Added column:

Custom= DateTime.AddZone([DeliveryDateTime], -11) (arbitrary datetimezone function, offset doesn't matter, before or after the From function doesn't matter)

Result:

If I remove the Custom column in another step, the DateTimeZoneLocal resets back to UTC (+0).

 

Can anyone else recreate this behavior? Is there any way to describe this except as a bug?

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional

    Hey mr_fnord ,

     

    I think you have to put the "cultural" parameter for the DateTimeZone.From function:

    DateTimeZoneLocal = DateTimeZone.From([DeliveryDateTime], "en-US")

     

    Otherwise Power Query doesn't know which local date this is.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • mr_fnord's avatar
      mr_fnord
      Frequent Visitor

      Adding a culture works, but why the inconsistent behavior based on other datetimezone functions?

       

      • selimovd's avatar
        selimovd
        Most Valuable Professional

        Hey mr_fnord ,

         

        what do you see as inconsistent behavior?

         

        Best regards

        Denis