ODATA Filter Query - Date field is less than (or passed) Today's date

[rhiheu]

Hi,

I would like to apply an ODATA Filter Query to my SharePoint Online 'Get items' action. I have a SharePoint Date & Time field called 'Booking Date' and I would to filter the results based on when that date has passed (the Power Automate will run once a day).

So far I have:

BookingDate lt '@{formatDateTime(utcNow(),'dd-MM-yyyy')}'

But it's not finding any matches.

Does anyone have any suggestions? Any help appreciated, thanks. 

[v-bacao-msft]

Hi @rhiheu ,

You could try to change the format to 'yyyy-MM-dd'.

Best Regards,

[rhiheu]

Hi @v-bacao-msft, thanks for the suggestion. I've tried that:

BookingDate lt '@{formatDateTime(utcNow(),'dd-MM-yyyy')}'

But unfortunately now it says 'String was not recognised as a valid DateTime. Creating query failed.'

I'm not sure what to try next 🙂

[v-bacao-msft]

Hi @rhiheu ,

What is the type of AptDate?

Best Regards,

[rhiheu]

Sorry that was a typo, now fixed. BookingDate is a SharePoint Date & Time field.

[rhiheu · Accepted solution]

Ah sorry, It was a naming mistake - it was 'Booking Date' in SharePoint and I thought I could just remove the space to make it 'BookingDate' in the ODATA Filter Query but it looks like that doesn't work. 'Booking_x0020_Date' doesn't work either. So instead I've just created a new field called 'BookingDate' and that works. If anyone knows how to get columns with spaces to work please let me know, but in the meantime I'll mark this as solved. Thanks @v-bacao-msft for your support!

[Vitss]

You can always add a (temporary) step in your Flow that crates an item. in the field 'Booking Date' just type some dummy text. Then click on the 3 elipses, choose peek code and search for your dummy text.

In front of that you can find the SP name of the column name. it depends from form to form.