odatasharepoint-onlinepower-automate

Comparing dates in Get Items step using ODATA QUERY

I have a Get Items step in my power automate flow, and am trying use an ODATA filter query to restrict the date field ImplementationDateTime - essentially I want records where that field is in the last 7 days (before I start having fun dealing with weekends).

so in my Get Items I have tried to use 

CABType eq 'Standard' and CABStatus eq 'Submitted' 
and (formatDateTime(ImplementationDateTime,'yyyy-MM-dd') gt '@{addDays(utcNow(),-7,'yyyy-MM-dd')}')

like this: enter image description here

unfortunately, I keep getting The query is not valid errors

This is what the query looks like at runtime:

CABType eq 'Standard' and CABStatus eq 'Submitted' 
and (formatDateTime(ImplementationDateTime,'yyyy-MM-dd') gt '2024-04-09')

The other fields, CABType and CABStatus are both string/text fields and never cause a problem

The other parts of the Get Items step have worked fine for over a year (using the variable for the site)

so what can I do to get this working? Is it possible to filter a Date and Time column like that?

Solution

  • There is no need to use formatDateTime around the DateTime column.

    You can try this:

    CABType eq 'Standard' and CABStatus eq 'Submitted' and ImplementationDateTime gt '2024-04-09'

    And like you said, the date can be dynamic with this expression:

    CABType eq 'Standard' and CABStatus eq 'Submitted' and ImplementationDateTime gt '@{addDays(utcNow(),-7,'yyyy-MM-dd')}'

    References: