GetOnUpdatedItems_V2 returns different date and time format compared to GetOnUpdatedItems_V2 when triggered from same SQL Table

[SSlemmen]

Hi!

I am using Power Automate to create and update items in a Sharepoint list when a row is added or changed in a SQL database.

GetOnNewItems_V2 gives following outptus for date:

"2023-01-20T00:00:00"

GetOnUpdatedItems_V2 gives following outputs for same date:

"2023-01-22T00:00:00Z"

GetOnNewItems_V2 gives following outputs for a time field:

"18:40:00"

 GetOnUpdatedItems_V2 gives following outputs for same time filed:

"PT7H"

 It seems like the format is not changed in the SQL server when the time or date is updated.

Anyone knows what can cause this?

Thanks in advance.

[nitishsh91 · Accepted solution]

Hi @SSlemmen 

The different outputs you're seeing for the date and time fields in your Power Automate flow are likely due to differences in the data type and format of the fields in the SQL database and SharePoint list.

When the data is being retrieved from the SQL server, it may be in a specific format and data type (e.g. datetime or time) that is not recognized as the same format in SharePoint. The "PT7H" in the output for the time field is an ISO 8601 duration format, which could indicate that the field in the SQL server is of the "time" data type, whereas SharePoint may be expecting a different format.

To resolve this issue, you can try to modify your flow to convert the date and time fields to the correct format and data type before updating the SharePoint list. For example, you can use the "Convert time zone" action to convert the date and time fields to a specific time zone, or use the "Format date/time" action to convert them to a specific format.

Another solution would be to modify the SQL Server table and make sure the date and time fields are in the format that SharePoint is expecting.

Additionally, it could be that the SQL server is in a different time zone, you should check this and convert the time zone accordingly.

In order to troubleshoot this issue further, it would be helpful to see the specific schema of the SQL table and SharePoint list, as well as the actions in your Power Automate flow.