0

I have a custom list with field of type DateOnly named ExpiryDate:-

enter image description here

Here is the site regional settings:-

enter image description here

now i have noted if we change the ExpiryDate to accept Date-&-time then we change it back to Date-Only, the date stored in the SharePoint will have a time associate with it. so when we want to get items using the REST API with expiry date equal for example 2023-05-07 we will get wired results, because SharePoint will only search the items created at 2023-05-07T00:00:00Z.. so is it safer to get the items using the Api query as follow: -

/items?$filter=(ExpiryDate ge '2023-05-07T00:00:00Z' and ExpiryDate le datetime'2023-05-07T23:59:59Z')

instead of:-

/items?$filter=(ExpiryDate eq '2023-05-07')

Any advice?

Improve this question

1 Answer 1

Reset to default
1

The issue with using 'ge' + 'le' instead of 'eq' is that you can get into max item per filter limit.

You can get a max of 5000 results for a single filter, once you have more than 5000 elements that match a given filter, the api will return an error.

For example, if you have more that 5000 elements which date is more than > 7/5/2023. If you try to use the following filter (ExpiryDate ge '2023-05-07T00:00:00Z' and ExpiryDate le datetime'2023-05-07T23:59:59Z') the api will crash because you have more than 5000 elements which match your first filter ExpiryDate ge '2023-05-07T00:00:00Z'.

But if you use the eq filter -> "DATEFIELD eq datetime'2023-05-07T00:00:00Z'". This will return all elements which are exactly on that day, since there are no more than 5000 elements which match that exact date.

You have to be careful with your filtered queries so your filters dont match more than 5000 results.

Update: I was wrong, GE + LE does work. If you have less than 5000 results between those two filters, results will return.

Improve this answer
6
  • thanks for the note which is very important. but i am doing the filter query inside the Power Automate GetItems do you know if i will face the same issue ?
    – microsoftdeveloperdesigner
    Commented Apr 13, 2023 at 19:41
  • now based on my test your comment on ge & le is not accurate. for example, i tried this query $filter=ExpiryDate ge '2023-07-30' i got this error The attempted operation is prohibited because it exceeds the list view threshold., but when i add the and as follow ExpiryDate ge '2023-07-30' and ExpiryDate le '2023-08-02' i got 100 results.. so if the first query exceeds 5000 this will not raise an error if we use and
    – microsoftdeveloperdesigner
    Commented Apr 13, 2023 at 20:41
  • 1
    Well, I retested what you told me and you are kind of right. My confussion came because SharePoints default date fields (Created and Modified) do produce this issues. Looks like you are free to use ge + le then!
    – Vertamin
    Commented Apr 14, 2023 at 9:24
  • yes correct your behavior will happen on the Modified date. do you know why this is happening on the modified date and not on the expiry date?
    – microsoftdeveloperdesigner
    Commented Apr 14, 2023 at 18:50
  • 1
    Because modified is the default SharePoint field and Expiry date is a custom indexed field. I guess the backend does not behave the same way for this type of requests.
    – Vertamin
    Commented Apr 14, 2023 at 23:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.