Answered

ODATA filter query for date - no outputs

(0) Share

Report

Posted on by Community Power Pla...

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?

All responses (7)

Answers (1)

Carls Sagan 8 on at

Like (0)

Report

ODATA filter query for date - no outputs

Can anyone explain why the date expression needs to be in single quotes? Is there any microsoft documentation that describes how this particular oData rule?
Community Power Pla... on at

Like (0)

Report

Re: ODATA filter query for date - no outputs

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.
Verified answer

Pstork1 66,879 Most Valuable Professional on at

Like (1)

Report
Re: ODATA filter query for date - no outputs

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
Community Power Pla... on at

Like (0)

Report

Re: ODATA filter query for date - no outputs

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.
Community Power Pla... on at

Like (0)

Report

Re: ODATA filter query for date - no outputs

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.
Pstork1 66,879 Most Valuable Professional on at

Like (0)

Report

Re: ODATA filter query for date - no outputs

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.
Sundeep_Malik 6,480 on at

Like (0)

Report

Re: ODATA filter query for date - no outputs

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.