November 27, 2018

Flow – Get SharePoint list items by the current date using ODATA

The built in Flow ‘Get Items’ and ‘Get Files (Properties Only)’ actions are great for retrieving a filtered set of items from SharePoint lists and libraries using an ODATA query except when it comes to working with dates!

Sometimes you may wish to query list items/files based on the today’s date e.g. Created, Modified or a custom date column. You could of course retrieve all your list items and do the date comparison in a Flow condition with an expression but then you may be retrieving more items than you actually need to work with, meaning your Flow takes longer to run and consumes more resources.

In theory we should just be able to use the eq (equals) operator to compare the date to the today’s date (utcNow() in Flow) however I found that this does not work. I think it must be something to do with what format the date is in the JSON body returned from the Get Items action i.e. it may contain the full time date/time portion and not just the date.

So my solution for querying by the current date is to use a combination of the ge (greater than or equal) and lt (less than) operators. The trick is to check if the date value from the list item is greater than or equal to today (utcNow() in Flow) and less than today + 1.

Here is an example query (where ‘MDCNextReviewDate’ is my SharePoint column):

MDCNextReviewDate ge ‘@{formatDateTime(utcNow(), ‘yyyy-MM-dd’)}’ and MDCNextReviewDate lt ‘@{formatDateTime(addDays(utcNow(),1), ‘yyyy-MM-dd’)}’

If anyone knows how to get the eq operator to work correctly in this way then please let me know so I can update this post 🙂

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

WP to LinkedIn Auto Publish Powered By :