Difference between flow date and sharepoint list

[LearningPA23]

I am getting data from SQL Server and one of the column has Date and Time, when I use that field to update sharepoint List column date and time, same name in column. The times are different.

 How can I fix this issue?

[SudeepGhatakNZ]

Can you check if your SharePoint site is in the same timezone?

Change regional settings for a site - Microsoft Support

[LearningPA23]

Hi SundeepGhataKNZ,

Appreciate your feedback.  I did check that and the site is set for the correct time zone (Easter Time Zone). That unfortunately does not fix the problem.

[SudeepGhatakNZ]

Are you able to post a screenshot of the Update action in designer mode?

[LearningPA23]

As you can see the date is fed by the compose above, this is the formula: 

if(empty(items('Apply_to_each')?['NextMeetingStartDate']),null,formatDateTime(items('Apply_to_each')?['NextMeetingStartDate'],'MM/dd/yyyy hh:mm tt'))

It comes out well just the time zone is the issue.  I had the timezone format in there but that didn't work either, also tried put the "G" and that didn't work.

[SudeepGhatakNZ]

@LearningPA23 ,

In your Compose action use the UTC format

formatDateTime('Your SQL Date','yyyy-MM-ddThh:mm:ssZ')

[LearningPA23]

still didn't work, time and zone shows 5:00 AM when it should be 9:00 AM

[SudeepGhatakNZ]

What is the output of your Compose action with the UTC format ON?

formatDateTime('Your SQL Date','yyyy-MM-ddThh:mm:ssZ')

 Can you provide the value?

Trying to understand where the problem is, SQL or SP

[LearningPA23]

Value with your recommended change
Value before format change

[SudeepGhatakNZ]

It is definitely to do with time zone

Can you try this?

convertTimeZone(triggerBody()?['YourDate'],'UTC','Eastern Standard Time','yyyy-MM-dd HH:mm:ss')

[LearningPA23]

Now I get 1:00 AM as the time. And the null error.

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'convertTimeZone' expects its first parameter to be a string that contains the time. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#ConvertTimeZone for usage details.'.

Shows 5:00 with no AM/PM but shows 1:00 AM on the sharepoint list

[LearningPA23]

This still isn't working as you can see from the image I posted earlier.  It still posts the wrong time.

[SudeepGhatakNZ]

@LearningPA23 

The null exception occurs because some of your dates may be empty or null.

To address this, consider adding a condition that checks for null dates before evaluating the expression.

Additionally, I recommend verifying the time zones of your SQL server and SharePoint site.

If both are in the same timezone, using the UTC format should work seamlessly.

[LearningPA23]

Timezone's are the same.

[Chriddle]

Your SQL action returns a local time, while Sharepoint's Update item action expects UTC.

Use convertTimeZone to fix it:

convertTimeZone(triggerBody()['YourDate'],'Eastern Standard Time','UTC')

[wskinnermctc · Accepted solution]

Power Automate is going to read the SQL DateTime as UTC even if it is not.

For Example you have a SQL DateTime of: September 14, 2023 9:00 AM

When that comes into power automate it will be turned into: 2023-09-14T09:00:00Z

Z is a timezone of UTC. Whenever a time ends with 'Z' it will be considered as being in the UTC timezone.

So if you put 2023-09-14T09:00:00Z into a SharePoint that is in Eastern Time Zone, then the SharePoint will display 9/14/2023 5:00 AM.

This is because when it is 9:00 AM in the UTC timezone, it is 5:00 AM in the Eastern Time Zone when considering daylight savings time.

So to get your datetimes to work from SQL, you need to understand that they are immediately put into the UTC timezone when they are pulled into Power Automate since the 'Z' is added on the end. (So basically they are incorrect from the beginning).

https://powerusers.microsoft.com/t5/General-Power-Automate/Adjust-DateTime-values-from-Excel-to-a-UT...

Above is an example of how to convert dates. You are primarily trying to get rid of the initial 'Z' that is automatically applied. Then convert it to the correct UTC time.

So if you have a SQL DateTime of: September 14, 2023 9:00 AM

When that comes into power automate it will be turned into: 2023-09-14T09:00:00Z

You need to remove the 'Z' so that it is: 2023-09-14T09:00:00

Then convert it from Eastern Time Zone to UTC so that it will be: 2023-09-14T13:00:00Z which is 1:00pm UTC

Then put that converted UTC datetime 2023-09-14T13:00:00Z into the update sharepoint item.

The SharePoint display will convert the UTC datetime into your local time of 9/14/2023 9:00 AM

Z is a timezone - it is not a format - once a time has a 'Z' on the end, it is in the UTC timezone no matter what.