A community member has associated this post with a similar question:
Rest APIGetItems is not returning all the records

Only moderators can edit this content.

Confusing results when filtering a Date field using REST API

john john 1,026 Reputation points
2023-05-01T12:02:46.9+00:00

I have a SharePoint online site collection which use London time zone:-

enter image description here

and i am using a service account which use the same time zone inside its profile:-

enter image description here

inside a list I have a field named Expiration date of type Date only:-

enter image description here

now using the list forms >> when I add a new item using the Calendar and I set the Expiration date to let say 14 May 2023 >> the date will be saved/viewed as 14 May 2023 inside the UI (list views) >> but will be saved as 13 May 2023 23:00:00 inside the rest API (when I query it is using the rest API), as follow:-

enter image description here

enter image description here

Now i think i know the reason for this, is that SharePoint stores the Date based on UTC which is minus one hour from London time. but the strange/confusing thing is that if i write this Filter inside the rest Api I will get the item:-

/_api/lists/getbytitle('Certifications')/items?$filter=(ExpirationDate ge '2023-05-13T00:00:00Z') and (ExpirationDate le '2023-05-13T23:59:59Z')

and also if i write this query i will also get the same item:-

/_api/lists/getbytitle('Certifications')/items?$filter=(ExpirationDate eq '2023-05-14')

so not sure if this item has its expiry date on 13 May or on 14 May from SharePoint REST API point of view? also can i assume that if i pass the time inside the API filter then SharePoint will not perform any conversion to UTC?

Microsoft 365 and Office | SharePoint | Development
Microsoft 365 and Office | SharePoint | For business | Windows
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    1 deleted comment

    Comments have been turned off. Learn more