cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

ODATA filter query for date - no outputs

My flow uses a manual trigger to start with a date input (Date added to list). Then I've got a Get items from SharePoint action.

I want to filter so that the Created date in the list equals the input date.

I have tried Created eq 'Date added to list' but it doesn't come up with any outputs even though I have one item with that date.

I thought it might be because Created is a date and time, so have tried using the startswith() and substringof() functions but it didn't like those. I also tried using the formatdatetime expression even though it already seems to be in the correct format.

Is there anything else I can try?

1 ACCEPTED SOLUTION

Accepted Solutions

Two things.

1) When testing dates I don't think you should be using the single quotes.

2) You need to use Created in both clauses.  Try the following.

Created gt 2022-11-08T00:00:00Z and Created le 2022-11-09T00:00:00Z


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

View solution in original post

6 REPLIES 6
Sundeep_Malik
Community Champion
Community Champion

Hey @Anonymous 

 

I think this might be happening because the type of created will be date and time. So, you must be giving just the date in the manual trigger and not the time.

So, you can try closing the option of time by going into the created column edit settings if possible.

Thats my 1st solution for you. If this does not work, we will go further.

The created date time field is a system field and can't be changed to Date Only.  Instead of checking for the date being equal you need to use a range.  The date is greater than yesterday at midnight and less than today at midnight.



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

Thanks Sundeep - I think that's the issue too. As far as I'm aware the Created date cannot be updated to date only as it's a system column. But that has given me the idea to create a calculated column based on Created and use that in the query instead - if it's possible to use a calculated column.

Anonymous
Not applicable

Thanks @Pstork1 - have also tried that but get this error:

The expression "Created gt'2022-11-08T00:00:00Z' and le '2022-11-09T00:00:00Z'" is not valid. Creating query failed.

Two things.

1) When testing dates I don't think you should be using the single quotes.

2) You need to use Created in both clauses.  Try the following.

Created gt 2022-11-08T00:00:00Z and Created le 2022-11-09T00:00:00Z


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

Thank you. I'd missed the need to use Created in both clauses. That worked - I got the output I needed.  The single quotes were still required though:

calvares_0-1668008287624.png

 

I wasn't able to use a calculated column in Filter Query by the way.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,925)