Problem with OData date filter

[TheShihan]

I have a Flow where I have an action "Get items" (pagination turned on) to give me only a given set of revenue items, they must have a date lower than a given date (revenue date of Monday from last week) and in the path of the item it must have the word "Offen" (meaning "open"), because we have setup different folders on the list with different permissions (open folder the tenants have write access and in the normal folder they only have read access).

I noticed that the action/filter gives me back to many items. Altough I specify the correct last Monday date and use "lt" as an operator I also get items in the result output that have that exact same date.

To make things more clear, here an example from a histroy run log:

the evaulated filter query looks like this: (Datum lt datetime'2021-03-08T00:00:00.0000000Z') and (substringof('Offen',FileDirRef))

(2021-03-08 is a Monday, I want to close everything that is earlier than this date, not earlier and this date).

But in the output I find for example this entry:

{
"@odata.etag": "\"1\"",
"ItemInternalId": "120129",
"ID": 120129,
"Title": "Eintrag",
"Jahr": "2021",
"Kalenderwoche": "10",
"Datum": "2021-03-08",
"MieterReference": {
   ...
},
...
"{FilenameWithExtension}": "Eintrag",
"{Path}": "Lists/Umsatz/99999/Offen/",
"{FullPath}": "Lists/Umsatz/99999/Offen/120129_.000",
},

You see, the entry has a "Datum" value like this: "Datum": "2021-03-08"

So, why isn't it filtered out from the result? I checked if maybe I missinterpreting the OData "and" operator the wrong way, so that "and" means rather or (left part OR right part) but my results only contains item where "Offen" is part of the path (and I know it has items that don't have this in the path). So it looks that at least the right part of the filter is correctly working but I don't see why the date part is not working.

I guess it shouldn't be a timezone issue, since the query output looks like it correctly uses the UTC time.

By the way. "Datum" is a date field in the list of SharePoint Online and it is set to "date only".

Any ideas?

[TheShihan · Accepted solution]

After fiddling around a bit more, it looks like this may be indeed some UTC/timezone issue. When I manually make the OData query for this item in my browser and have a look at the XML result I get the following values:

<content type="application/xml">
<m:properties>
...
<d:Datum m:type="Edm.DateTime">2021-03-07T23:00:00Z</d:Datum>
...
</m:properties>
</content>

So, I get it that when I, as a swiss user enter the 8th March of 2021 it is saved as 2021-03-07T23:00:00Z, because SPO works with UTC, and SharePoint automatically converts the time back and forth.

OK, so I guess I have to use the "convertToUtc()" function to convert my "Last Monday date" value to UTC and then use this result for my filter.

(But still strange that the Flow history output shows me the date as "2021-03-08", why is it converted there?)

I will try this out and let you know.

[v-litu-msft]

Hi @TheShihan,

Any DateTime type of columns we create within SharePoint, are stored in UTC format internally. Any custom implementation which accesses the DateTime values programatically, will get the DateTime values returned in UTC as well. So, when a SPFx webpart tries to read list items with DateTime field values, using PnPjs, REST API or any other supported models, the DateTime values are always returned in UTC format. Due to this we might be seeing appropriate DateTime values within SPFx webparts.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheShihan]

To give an update, I was indeed able to solve the issue by converting the date for my OData filter from my local timezone (timezone setup in the regional settings of SharePoint) to UTC using the "Convert To Timezone" action.