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

[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.

[manuelstgomes]

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

[bibhupanigrahi]

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.

[trentq]

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

[trentq · Accepted solution]

Ending up getting to work with:

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

[korebreach2022]

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').