“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.
I had high hopes that you’ve provided the solution to my frustrating problem. I have an identical need as your “Filter ‘Date Only’ Column”. My filter expression is “ArchiveTargetDate lt ‘utcNow(‘yyyy-MM-dd’)’ where ArchiveTargetDate is a date-only field in a SharePoint list. However, when I run it I get the following error: The expression “ArchiveTargetDate lt ‘utcNow(‘yyyy-MM-dd’)'” is not valid. Creating query failed.
clientRequestId: 5c4f14e6-4479-48fc-a50e-7e56b8278870
serviceRequestId: 5c4f14e6-4479-48fc-a50e-7e56b8278870
This has been driving me crazy for days. Any advice would be appreciated!
Hello Nestor,
the whole utcNow(…) part must be entered as an expression in the Filter Query, not as a plain text.
I have a similar error as Nestor when running the associated flow, where the response suggests the following is not valid as filter query:
Last-Modified ge ‘@{subtractFromTime(utcNow(),5,’Minute’,’R’)}’
I did enter the part within the ” as an expression. Is the expression too complex? It seems like the response is able to get a timestamp formatted the same as the Last-Modified column, so it’s as though it’s unable to compare the two dates. I assume there’s no way to have an expression on both sides of the operator?
Hello RV,
I think it doesn’t like the ‘R’ date format. When you work with dates from SharePoint you should always stick to the ISO format – there’s no need to format the date, you can keep it in the default format: subtractFromTime(utcNow(),5,’Minute’)
And your assumption is correct, you can’t have an expression on both sides, the left side must be always the column as it is.
This reminds me of my favourite bug ever. I’d written some VBA that utilsed the current date. It seemed to work about one in 5 times but throw an error the rest of the time.
Turns out I had used a date format string of dd-mm-yyyy – should have been dd-MM-yyyy. Which meant that if the code ran when the current time was between hh:01 and hh:12, the code would complete (but only with a 1 in 12 chance of giving the right month). When the minutes were between hh:13 and hh:00, the code would crash with a date format exception.
(PS I haven’t written VBA in 2 decades so it may be that the working minutes were 0-11 – don’t shoot me!
Hello Toby,
that’s a good one, I’ve seen it a few times already and it’s always so much fun debugging before you find it… 🙂
Is it possible to filter my list to find results where the Date field IS blank? I only want to send a reminder to people to close out their assignment if the DateAssignmentEnd is yesterday or earlier, and the DateCompleted field is still blank. I have tried everything I can think of and nothing has worked!
Never mind… of course as soon as I posted my comment I got it to work. Seems that moving my blank comparison to be the first condition then everything finally worked:
DateCompleted eq null and DateAssignmentEnd lt ‘@{utcNow(‘yyyy-MM-dd’)}’
I have the following issues:
I set a recurrence trigger > followed by get items from sharepoint. In my Filter Query I filtered today’s date using ge utcNow and it addDays(utcNow(),1) as expressions. The query returns the list that I wanted. However, the column for time shows UTC values with a different format. I ultimately would like the format to show in the time column as MM-dd-yyyy, hh:mm.
I cannot seem to convert using actions or expressions either. My guess is that I have to change the column. However, the array does not let me change with a string, because the values in the time column have become strings after using Get Items.