OData Filter OR operator not working with dates

(0) Share

Report

Posted on by BenMunk

114

Am I missing something really simple?

Inside a flow, inside of Sharepoint Get Items action

This returns multiple items

DateComplete gt '@{outputs('Compose')}'

This returns multiple items

StaffChangeToCompleteDate gt '@{outputs('Compose')}'

This returns nothing

DateComplete gt '@{outputs('Compose')}' or StaffChangeToCompleteDate gt '@{outputs('Compose')}'

For context - here's where I'm using it.

Categories:

General topics

All responses (8)

Answers (0)

Sort by

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@BenMunk ,
Try comparing the dates in the same format using the formatDatetime function. The format you should use is yyyy-MM-dd.
Like:
DateComplete gt formatDateTime('@{outputs('Compose')}','yyyy-MM-dd')
BenMunk 114 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for you suggestion, but I seem to be getting a syntax error with that "The query is not valid."
BenMunk 114 on at

Like
a
(0)

Report
Copy link

Link copied!

@SudeepGhatakNZ I tried formatDateTime on the Compose action. Syntax worked fine, but still same problem with the filter - each clause fine by itself, as soon as combine the clauses with the "or" operator, I get no results.
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Can you upload a screenshot?
BenMunk 114 on at

Like
a
(0)

Report
Copy link

Link copied!

First screenshot shows setup and output with "or" operator - no results. Second screenshot shows same thing without the "or" statement - multiple results.
BenMunk 114 on at

Like
a
(0)

Report
Copy link

Link copied!
Its possible that its a bug.
It seems I can run
Date type column filter by itself (eg. 'date' gt 'yyyy-MM-dd') - works fine
OR operator with 2 Non-date types (eg. 'id' eq 'x' or 'status' eq 'y') - works fine
OR operator with mix of Non-date type and Date type (eg. 'id' eq 'x' or 'date' gt 'yyyy-MM-dd') - disregards the date filter - only returns (id = x)
OR operator with 2 Date types (eg. 'dateA' gt 'yyyy-MM-dd' or 'dateB' gt 'yyyy-MM-dd' ) - returns nothing.
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Interesting! I will have to try that out. I use Or all the time. Can't find an example where two dates are involved.
BenMunk 114 on at

Like
a
(0)

Report
Copy link

Link copied!

Would be interested to hear if its happening for you. In the end I had to use non-date columns to achieve my needs. If it is a bug hopefully its rectified soon.