ODATA filter query for date - no outputs

[Anonymous]

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?

[Sundeep_Malik]

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.

[Pstork1]

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.

[Anonymous]

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]

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.

[Pstork1 · Accepted solution]

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

[Anonymous]

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:

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