Answered

Filter End Time is greater than or equal to today AND Start Time is less than or equal to today

(0) Share

Report

Posted on by trentq

Hi, could I please get some help filtering a SharePoint Get Items to End Time is greater than or equal to today AND Start Time is less than or equal to today? The field does have date and time, so need to cover the entire 24 hours of that day.

Thanks in advance.

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

manuelstgomes 6,625 on at

Like (0)

Report

Hi @trentq

Please try the following:

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Was this reply helpful? Yes No
bibhupanigrahi 240 on at

Like (0)

Report

Hi @trentq, you can use a loop with a condition to filter the outputs of Get Items and save matching items to an array variable, as shown in the picture below. I have used a SharePoint task list here, and it works fine a as long the date fields are not empty.
Hope this helps.

Was this reply helpful? Yes No
trentq 35 on at

Like (0)

Report

Hi Manuel

Thanks for the response, I'm getting this error:

The expression "End Time gt 2020-11-27T01:43:56.7056324Z AND Start Time It 2020-11-27T01:43:56.7056324Z" is not valid. Creating query failed.
clientRequestId: aa484683-494f-4935-ae12-6d8dd4dcaf91
serviceRequestId: aa484683-494f-4935-ae12-6d8dd4dcaf91

Was this reply helpful? Yes No
Verified answer

trentq 35 on at

Like (0)

Report

Ending up getting to work with:

EndDate gt '@{formatDateTime(addDays(utcNow(),-1))}' and EventDate le '@{formatDateTime(addDays(utcNow(),+1))}'

Was this reply helpful? Yes No
korebreach2022 21 on at

Like (0)

Report

I haven't been able to quite nail down the reason this works, but it does. I was facing almost the exact same problem. However, my dates in SharePoint were set to "Date only", rather than "Date and time". For some reason, even if you have SharePoint columns set to "Date only", things don't work as expected.

If I want to get items with end dates greater-than-or-equal to today, and my expression is EndDate ge utcNow(), you would think that would work. Instead, you may find (as I did) that you have to say EndDate ge addDays(utcNow(),-1). Why would you need to subtract a day? IDK.

As an example, one of my end dates was tomorrow. If I try to filter with EndDate ge utcNow(), that item was missed. If I filter with EndDate ge addDays(utcNow(),-1), it is included.

I could maybe understand if there was some hidden time information in the EndDate, and when the data was created in SharePoint, the current time is being held behind-the-scenes (even with a Date only column), but I confirmed the raw data being passed to Power Automate is date only. Weird.

Note that it also doesn't work without subtracting a day even if you use formatDateTime(utcNow(),'yyyy-MM-dd') or utcNow('yyyy-MM-dd').

Was this reply helpful? Yes No