Filter a Sharepoint Online DateOnly column in REST API without the time

Question

I have several date only columns (no time can be selected) that return either "YYYY-MM-DDT05:00:00Z" or "YYYY-MM-DDT06:00:00Z", which makes things odd and difficult to deal with.

Example:

I don't know why the timepart is different…

How, with REST API, can I filter these columns with only using the date part, and without worrying about the time part?

For example, if my field shows: "2021-09-19" I'd like to do the below filter:

$filter=ColName eq datetime'2021-09-19'

Using a date range to cover the whole day (.e.g $filter=ColName ge datetime'2021-09-19T00:00:00Z' and ColName lt datetime'2021-09-20T00:00:00Z') won't work with a list with more than 5,000 items.

I could also test the T05:00:00Z and T06:00:00Z but it looks like a dirty hack, and I'm not sure this is tied to my own timezone?!

I need to use the /items end point (I cannot use the /getitems with CAML query).

Is there a parameter that I missed to make it work?

Answer 1

The query with GE and LT is correct. The 5000 items limit is another thing:

1. you need to have to set an index (if you would use graph API, you never can filter on non-indexed columns)
2. filters seem to be applied sequentielly: order matters - if the first date comparison return >5k items, it will not work

Btw: the time is comming from the timezone of the user. If an +01:00 user enters 23.09.2022 via default SP Form, the value saved will be 22.09.2022T23:00:00Z

Above informations are correct but neither timezones (or handling dates without time/-zone information) nor huge SP data (5000+) handling are easy topics - already invested weeks in multiple projects.