Microsoft Flow: How to query Dynamics 365 date/time fields with UTC time conversion

Microsoft Flow has opened up a vast toolset for automating processes across different applications. However, simple actions like querying datetime fields can be unexpectedly cumbersome given Flow’s default UTC time zone setting.

This presented a challenge for me when I tried to create a scheduled report in Flow that queried records in Dynamics 365 based on a specific date range. When I first ran the Flow, the query pulled records based on the UTC time zone rather than the D365 local time zone and therefore wasn’t returning the expected records.

To solve this problem, I made use of Flow’s “Convert time zone” action to get the UTC start of day for my datetime query. Below are the steps you can take to query datetime fields according to local time.

For the first step, use the “Convert time zone” action to convert the built-in utcNow() function to our local time zone. The base time will be set as utcNow(), the source time zone will be UTC and the destination time zone will be your local time zone:

Now that we have converted utcNow() to local time, use this to get the start of day in our local time zone and then convert it back to UTC. To do this, utilize the “Convert time zone” action again. For the base time use the converted UTC time from the previous step within the built-in startOfDay() expression, as shown in the screenshot below. The source time zone for this step will be your local time zone and the destination time zone will be UTC:

For the final step, plug the “UTC Start of Day” value into our query. I used the Common Data Service’s “List records” action in my Flow but you can also use the corresponding action for the Dynamics 365 connector. My goal was to query a Dynamics 365 Field Service environment to fetch all bookings scheduled for the following day to create a report that will be emailed out each night with a list of the next day’s bookings. To specify the appropriate date range, I used an oData expression in the “List records” filter to pull booking records where the start time is greater than “UTC Start of Day” + 1 day and less than “UTC Start of Day” + 2 days. Below is the full filter expression as well as a screen shot of the Flow action: (starttime gt addDays(body('UTC_Start_of_Day'),1,'yyyy-MM-ddTHH:mm:ssZ')) and (starttime lt addDays(body('UTC_Start_of_Day'),2,'yyyy-MM-ddTHH:mm:ssZ'))

Want to learn more? Get in touch with us and we'll be happy to help.