GetItems from a SharePoint list will convert the DateOnly field to the site timezone?

(0) Share

Report

Posted on by johnjohnPter

1,811 Super User 2024 Season 1

I have a SharePoint list with a column of type DateTime (DateOnly) field named StartDate1, and our site collection has UTC+2 time zone.

Now when i directly use SharePoint REST API "/_api/web/lists/getbytitle('bookingcalendardetails')/items(1238)", i will get this value for the DateOnly field:-

<d:StartDate1 m:type="Edm.DateTime">2024-10-27T22:00:00Z</d:StartDate1>

But when i run the GetItems action, it will convert the above Date into 28/10/2024, which is based on the site time zone?

So can i conclude that GetItems is smart enough to convert the value returned from SharePoint API into the site time zone regional settings? Although for the Created date the time returned by the GetItems will be in UTC and not in the site time zone as follow:-

So this is a bit confusing? the question is how GetItems treat the DateOnly fields and the Datetime fields in respect to the site local time zone?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Ellis Karim 11,691 Super User 2025 Season 2 on at

Like (0)

Report
Hi @johnjohnPter,

I encountered the same questions back in October and wrote a blog post about it.

SharePoint always stores dates in UTC (Coordinated Universal Time).

Power Automate does not perform any time zone conversion in OData queries; it simply passes date filters to SharePoint as they are. SharePoint Online then interprets and processes these filters based on two factors:

the presence of the 'Z' suffix (indicating UTC)

and the site's time zone configuration.


If the date-time filter includes a 'Z' suffix, SharePoint treats it as UTC.

If the 'Z' is absent, SharePoint assumes the date-time is in the site's configured local time and converts it to UTC accordingly. Read more here and let me know if they match your experiences:

Why Are My SharePoint Dates Wrong in Power Apps and Power Automate? – Ellis Karim's Blog

Avoiding Time Zone Pitfalls: Using OData Date Filters in Power Automate’s Get Item(s) Action for SharePoint – Ellis Karim's Blog

Ellis

https://elliskarim.com/

Was this reply helpful? Yes No
johnjohnPter 1,811 Super User 2024 Season 1 on at

Like (0)

Report

@@EKarim

Thanks for the reply.. but i am asking a different question, i am not asking about searching the sharepoint list inside the GetItems, i am asking about the response i get from this action inregards to the timezone

Was this reply helpful? Yes No
Ellis Karim 11,691 Super User 2025 Season 2 on at

Like (0)

Report
Ok, I think I see what you mean:

EndDate = Date Only Column

Get Item Action returns "EndDate": "2024-12-03" Send an HTTP request to SharePoint returns "EndDate": "2024-12-03T00:00:00Z"

Expecting Get Item to return "2024-12-03T00:00:00Z" and not "2024-12-03"

and

EndDate = Date Time Column, set to 2024-12-03 14:00 (Region = London, currently same as UTC)

Get Item action returns "EndDate": "2024-12-03T14:00:00Z Send an HTTP request to SharePoint returns "EndDate": "2024-12-03T14:00:00Z"

Ellis

Was this reply helpful? Yes No
johnjohnPter 1,811 Super User 2024 Season 1 on at

Like (0)

Report

@@EKarim

you are assuming the Region is London .. but in my case the region for my site is UTC+2.. i am not sure if you understood what i am asking for

Was this reply helpful? Yes No