Updating date/time columns in SharePoint lists using HTTP Request action in Power Automate

[Anonymous]

Hi, 

I'm trying to update my SharePoint list via HTTP Request action. I seems that there is a problem when dealing with date-time data. I get the following error:

As you see  below, I'm updating information from two columns of the list, the first culumn is a choice colum (AlertSystem) and it's working well, the problem is with the date time column(AlertTimestamp). I'm trying to update this field with  the current date. In order to do this, previously, I have created a variable that contains the current time through the next functions : formatDateTime(utcNow(),'MM-dd-yyyy')

Do you know how to deal with timestamps in HTTP requests ?

Thanks

[wskinnermctc · Accepted solution]

If you have a DateTime column in SharePoint, it will only accept the format of a full date time in an http request like this. So when you send it "AlertTimestamp":"01-26-2023" SharePoint is reading the "01-26-2023" as a text item. So you might as well be attempting to put "AlertTimestamp":"John Smith" in the DateTime column which obviously wouldn't work.

For your request there could be 2 approaches:

1. Send full utcNow() dateTime so the current time is included
2. Send double formatted utcNow() dateTime that would appear to be time of midnight 00:00:00

For option 1. You don't need to FormatDateTime for it to go into a SharePoint column that is DateTime. You can just use the utcNow() expression. The HTTP request output will look like "AlertTimestamp":"2023-01-26T14:37:03.3709583Z"; however, the datetime column in SharePoint will display however you have that column set (including your timezone settings for SharePoint).

For option 2. If you don't want the time section from the utcNow() and basically want it to just be a date with no time. You will have to change the time to midnight, so the output would look like "AlertTimestamp": "2023-01-26T00:00:00.0000000Z" in the HTTP request. This you need to format the utcNow() into a MM/dd/yyyy and then convert the timezone from UTC to UTC. The expression ConvertTimeZone() is basically an add hours function, but the benefit is the output is a time format.

convertTimeZone(formatDateTime(utcNow(),'MM/dd/yyyy'),'UTC','UTC')

(The Option 3 would be to have a Text type column in SharePoint that is called AlertTimeDate and then you would be able to send over a date in the format of "01-26-2023" and it would show exactly like that in the SharePoint column.)

Let me know if this works for you

[Anonymous]

Hi,

Option 1 worked well for my purpose. I just eliminated the variable and used the utcNow() function directly in the HTTP request action. See the details below:

This is the output  for both columns in the SharePoint list:

So no problem at all with the timestamp format. 

Thank you very much for the support, clear and elaborated answer.

Best Regards