cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhiheu
Advocate III
Advocate III

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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
rhiheu
Advocate III
Advocate III

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!

View solution in original post

6 REPLIES 6
v-bacao-msft
Community Support
Community Support

Hi @rhiheu ,

 

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

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rhiheu
Advocate III
Advocate III

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 🙂

 

Hi @rhiheu ,

 

What is the type of AptDate?

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rhiheu
Advocate III
Advocate III

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

rhiheu
Advocate III
Advocate III

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
Advocate I
Advocate I

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,879)