Unanswered

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?

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Sundeep_Malik 6,484 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report
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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Carls Sagan 12 on at

Like (0)

Report

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?

Was this reply helpful? Yes No