Quick tip - filter SharePoint items by date in Flow

A quick post on working with dates in Microsoft Flow, because I know I'll need this again and I didn't immediately find something on this when I looked.

When using Flow actions such as "SharePoint Get Items", it's frequently the case that you want to filter items by a date/time field. Often you want to grab items older (or newer) than a certain timestamp - perhaps to delete/archive/take other action on them.

The easiest way is to use the getPastTime function, ensuring you format it in the right way. A clause such as the following can be used in the "Filter Query" property of "Get items":

formatDateTime(getPastTime(1, 'Month'), 'yyyy-MM-dd')

Note that this format is designed to work with a date/time field in SharePoint which is configured for "date only" format:

For dates which include the time, you'll probably need to use formatDateTime with an alternative format (sorry, I didn't test that).

Notes on using getPastTime and similar functions

The signature of the "getPastTime" method is:

getPastTime(<interval>, <timeunit>, <format>?)

The list of possible values for "timeUnit" include:

"Second", "Minute", "Hour", "Day", "Week", "Month", "Year"

As you might expect, there's also a corresponding function called getFutureTime(). To use one of them in the "Get items" action, provide a filter query in OData format which uses the internal name of your date field - something like:

MyDateFieldInternalName lt formatDateTime(getPastTime(1, 'Month'), 'yyyy-MM-dd')

That would filter items to only those older than one month for example. In the Flow designer, this should look like this:

Hope that's useful. May all your Flows execute on time and without error!