Power Automate, SharePoint, Dataverse and DATES - Gotchas That'll Getcha

Dates... They are pains in the neck. They should be easy. But... They aren't. One of the biggest Gotchas I find with dates is moving between SharePoint and Dataverse. It should be easy right? I mean both come from Microsoft, they should play nice? Sometimes they do.. But, there are a few gotchas that you have to be aware of.

First... Dataverse and SharePoint both store dates in the UTC format. It looks like this: 2023-03-06T20:00:00Z. This date is the year, 2023, the Month, 03, the TIME ZONE, 06, and then the time, 20:00:00Z. In a friendly format this is March 3rd, 2023 at 2:00 PM Central Standard Time. If I pass this information from SharePoint to Dataverse, it works no muss no fuss. My date/time information is passed and written just as I expect it.

The very first gotcha is going to be from ANY input or datasource, you want your time to be formatted in this way. It saves a ton of time and heartache.

The problem comes when I pass a date from SharePoint that is JUST a date. In other words my column in SharePoint is configured for Date Only:

This column does not store any time information, and that means that a date of March 3rd, 2023 here looks like this: 2023-03-20T00:00:00Z, you will notice that there is NO time zone information. So this comes across as 12:00AM UTC, rather than 12 AM Central Time. That can be a big problem if I try to write this date to Dataverse.

To demonstrate, I created a quick flow that moved data from SharePoint to Dataverse. I passed two dates, an Unfriendly date with no time, and a Unfriendly date With time.

I created a Davaverse table with two date fields, Date and Time, and Just Date. Here is my flow:

The output in Datverse looks like this (SharePoint to Dataverse):

You'll notice that the first date matches exactly, but the second date is one day behind. This is because the Dataverse interface is showing me the date according to my locally configured time zone. Because the date comes in as midnight on 3/20/2023 UTC, that is 3/19/2023 Central Time USA.

This can be a big issue... There is no easy way in Power Automate to grab the Time Zone of the site collection during the write operation. What can be done is to add your time zone offset to the time when you do the write operation. However, that doesn't work well when you have a site or application that is used in multiple time zones. My best advice is to use a date and time field, or use a calculated column to add the time zone data, when you have a list that may write to any other datasource.

Coming back from dataverse to SharePoint, we use the following flow:

Using this data, that was input directly in from dataverse:

And we see this in SharePoint

Looking at the raw inputs:

we see that the time zone information in dataverse is set for the local time zone, and not stripped out.

We also notice that the date and times in SharePoint, no matter how they are formatted from the display point of view, accept the same kind of date format and are presented as expected.

So... In this example, we find that from Dataverse to SharePoint the writing of dates is pretty straight forward, but when moving from SharePoint to Dataverse, there can be issues with time zones.