cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenMunk
Helper II
Helper II

OData Filter OR operator not working with dates

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.

Snag_2d5941ba.png

8 REPLIES 8
SudeepGhatakNZ
Resident Rockstar
Resident Rockstar

@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')

 

If my suggestion helped you, please give it a Thumbs up and mark it as a Solution so that it can benefit others in the community.

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

@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
Resident Rockstar
Resident Rockstar

Can you upload a screenshot?

If my suggestion helped you, please give it a Thumbs up and mark it as a Solution so that it can benefit others in the community.

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

 

Snag_2dca25ef.png

 

Snag_2dd07980.png

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
Resident Rockstar
Resident Rockstar

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

 

If my suggestion helped you, please give it a Thumbs up and mark it as a Solution so that it can benefit others in the community.

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.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (1,814)