Using Filter Query for dates 1 week old and older

[Anonymous]

Good morning

I have an SP list that members add to frequently with a PowerApp. I would like to have a recurring flow that will take all items that are more that 7 days old and 

• move those items to another list "Archive"
• Delete these old values in the active list

I have a flow similar to this, with the difference being the Filter Query. I don't know the syntax for the date. here is what I have so far.

[Pstork1]

Since you want items that are older you want to turn your OData query around

'Date Returned' lt FormatDateTime(AddDays(UtcNow(), -7),'u')

That will find all the entries where the Date Returned is prior to 7 days ago.

[Anonymous]

I still receive a "Query not valid" error. I must be missing something else somewhere.

What does the 'u' signify?

[Pstork1]

I suspect you aren't entering the formatDateTime() and AddDays() functions using the Expressions tab of the dynamic content box.  It looks like you are just typing them in in the screenshot.

The 'u' is the standard ISO8601 universal sortable DateTime format.

[Ajinder31]

Hi @Anonymous , If you're not familiar much with expressions then please try following code in Filter query

'Date Returned' lt '@{formatDateTime(addDays(utcNow(), -7),'u')}'

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

[Anonymous]

I tried this and got the following error:

As you can see it does show a date that is 7 days old, but I don't know why there's a Z at the end of the time stamp

[Pstork1]

Single quotes shouldn't be around the Field name (Date Returned).  That's why its throwing an error.  That's the display name.  You need to look at the column details to find out what the internal name is and use that.  It won't have quotes and won't have a space in it.

[Anonymous]

Good morning, 

I made that adjustment and still have the same error

Without the apostrophe on the expression:

[Anonymous · Accepted solution]

I removed the space in "Date Returned" and the flow worked. I'm not sure why that made a difference

'DateReturned' lt '@{formatDateTime(addDays(utcNow(), -7),'u')}'

[Pstork1]

The difference is whether you are using the display name for the column or the internal name.  For OData you need to use the internal name.

[Anonymous]

I guess I was unaware of this and found out unintentionally. Thank you for you help!