Power Automate filter on SharePoint Date and Time column
“Is there any way to set an ODATA Filter Query to let Power Automate filter all SharePoint items where some date field is less or equal to today’s date?”
Filter on SharePoint Date and Time column is one of the more complicated ones in Power Automate. Unlike many other SharePoint columns, it’s often not just Date eq ‘Value’. There’s a difference if your date contains time or not, you must use the right date format, and often also some calculation. With so many places for a potential error it might be challenging to build the correct Filter Query. Let’s take a look then on how to do it.
All the examples below are working with a SharePoint column DateTime (DateTime is also its internal name).
Filter out items with empty date
The first filter is to get only items where the date column has some value. If you process the dates later using the formatDateTime(…) expression, you’ll see an error message for empty dates. The template language function ‘formatDateTime’ expects its first parameter to be of type string. The provided value is of type ‘Null’. You can avoid it by filtering out the empty dates right away.
DateTime ne null
Just type in the filter: your column’s internal name ne null. Don’t use null expression or single quotes, this is a special situation that doesn’t want it.
Filter ‘Date Only’ column
If your SharePoint column is ‘Date Only’, you should use date without time in the filter too. Since SharePoint will provide dates in ISO 8601 format, use the same format also for the ‘Value’. For date only column it means the format year-month-day: ‘yyyy-MM-dd’.
Don’t get confused by the date format in SharePoint, it’s displayed in the regional format but on the background it’s an ISO date.
You can use any operator on the ‘Date Only’ column: eq, ne, lt, le, gt, ge.
eq = equals
ne = not equal to
lt = less than
le = less than or equal to
gt = greater than
ge = greater than or equal toFor example, ‘Get items’ where date is in the past (less than today).
DateTime lt 'utcNow('yyyy-MM-dd')'
Here you must already use the proper Filter Query format: an expression and single quotes around it.
Filter ‘Date & Time’ column
If your date column contains also time, you should consider how you want to work with it. You’ll always need to include some time in the ‘Value’ date too. For example, let’s use a similar filter as above: date is less than utcNow() (date & time of the flow run). With ‘Date & Time’ column it’ll include also items with date earlier today. If you run the flow at 11am, it’ll include all today’s items with time before 11am.
It’s not some problem you must solve, it’s just something to keep in mind.
DateTime lt 'utcNow()'
Note: utcNow() in this example will contain also time. utcNow(‘yyyy-MM-dd’) from previous example will take only date (at 00:00:00 time); you can use it to get start of a day for filters in a ‘Date & Time’ column.
Related to the time part is also the selection of operators. For ‘Date & Time’ column it doesn’t make sense to use the operators: eq, ne. SharePoint ‘Date & Time’ column goes down to seconds, it’ll probably be never equal and always not equal to your ‘Value’ date.
Summary
Having the right format of the filter is often only the first (but critical) step when filtering by a date. You could see three different situations in this article, three different filters:
- filter empty date: no single quotes, no expressions
- ‘Date Only’ column: single quotes, expressions, ISO format (‘yyyy-MM-dd’)
- ‘Date & Time’ column: single quotes, expressions, ISO format, all dates have also time part
All the examples used only today’s date (& time) in all the filters, but you can use any date. You can add or subtract time period from a date to build reminders, even if the original date is in a calculated column.
You can even combine multiple filters to get items from a time period, e.g. a full day when working with date & time columns. And if your date filter is still not working, take a look on my old checklist of probable causes.