Unanswered

Filtering SPList between Dates

(0) Share

Report

Posted on by Robert_

Hello,

I am having an issue with filtering an SPList between two dates. I created a flow that queries an SPList and uses PowerApps to ask the user for their Date_From (YYYY-MM-DD) and Date_To (YYYY-MM-DD). I noticed that the date is being stored in UTC, i.e. 2023-11-15T17:18:42Z.

I used the convertTimeZone function to make sure that I could convert the time from UTC to Eastern Standard Time. But I am unsure how I use this to create the filter for the SPList. The user will be entering the date, expecting any record created within the EST date (i.e. 11/15/2023 - 11/16/2023). Therefore I need to take this date and look up all rows that match this date in EST format.

I hope this makes sense.

Screenshot 2023-11-16 at 8.02.28 PM.png

Screenshot 2023-11-16 at 8.02.00 PM.png

I have the same question (0)

All responses (8)

Answers (0)

lbendlin 8,363 Super User 2025 Season 2 on at

Like (1)

Report

Re: Filtering SPList between Dates

Where is your step to fetch the SP list? You can specify the filter right there in ODATA.

Was this reply helpful? Yes No
v-qiaqi@microsoft.com on at

Like (0)

Report

Re: Filtering SPList between Dates

Hi @Robert_,

You just need to configure in a Get items action as below:

Was this reply helpful? Yes No
Robert_ 36 on at

Like (0)

Report

Re: Filtering SPList between Dates

Hello @lbendlin

Here is how I am trying to fetch the data. The issue is that the dates in SPList are in UTC but I want to look them up in EST.

Was this reply helpful? Yes No
Robert_ 36 on at

Like (0)

Report

Re: Filtering SPList between Dates

@v-qiaqi-msft
Thanks but the issue I seem to be having is that the date is not converting to EST. For example, I have data that is from 11/16 but if I query 11/16, I get zero results. If I query 11/17 (to account for UTC), I get results. But the request was on 11/16.

Was this reply helpful? Yes No
lbendlin 8,363 Super User 2025 Season 2 on at

Like (0)

Report
Re: Filtering SPList between Dates

Your ODATA query is malformed. You need to specify the column name in each comparison.

Include the time zone conversion when specifying the values.

Was this reply helpful? Yes No
Robert_ 36 on at

Like (0)

Report

Re: Filtering SPList between Dates

I realized I left off the second Column name.

I cannot figure out how to do the time zone conversion. Can you give me an example?

Was this reply helpful? Yes No
Robert_ 36 on at

Like (0)

Report

Re: Filtering SPList between Dates

So I guess I am confused still. Do I even need to convert time? Using the filter, it seems to filter correctly, even though the "created" dates are in UTC, it appears to pull all records within the EST date I select.

For example, if I query 11-16-2023 - 11-16-2023, I get the correct rows, even though there are a few records with a 11-17 UTC but were created actually on 11-16.

So I guess this works as expected.

Was this reply helpful? Yes No
lbendlin 8,363 Super User 2025 Season 2 on at

Like (0)

Report

Re: Filtering SPList between Dates

yes, if your source data is in UTC (as it should be) then no conversion is needed. Otherwise use this: Converting time zone in Power Automate - Power Automate | Microsoft Learn

Was this reply helpful? Yes No