Using OData Filter Query to get open items with past due date from Sharepoint list using Microsoft Flow

Question

In my sharepoint list of items, I have a "Date Due" field, and a "Status" field. I'm trying to create a recurring flow that occurs once a week. It will grab all items that have a Status of "Open", and a Date Due of either that day or prior to it.

• Status contains either "Open" or "Closed".
• Date Due contains the due date of the item in a yyyy-MM-dd format.

This is my current flow:

1. Recurring Trigger of 1/Week.
2. Current Time
3. Get Items from a share point list.
   • So far for the filter I have Status eq 'Open' and Date_x0020_Due le Date(@{body('Current_time')})
     • @{body('Current_time') is the current time retrieved from step 2
4. I take those items and turn them into an HTML table
5. I send an email containing that html table.

Currently the filter for step 2 does not work. Status eq 'Open' is fine, however Date_x0020_Due le Date(@{body('Current_time')}) does not work.

I'm guessing this is because OData cannot convert the DateTime object of Current Time into a Date object and is unable to compare them.

How can I change this filter so it returns items that have both a Status of closed and a Date Due of that day or prior?

Restrictions:

• I cannot change the names of any fields.
• I cannot change the data type of any field (ex. make Date Due a DateTime Type).
• I cannot add or remove fields.

Answer 1

I somehow solved this.

My end flow looks like this:

1. Recurring Trigger of 1/Week.
2. Get Items from a share point list.
   • For the filter I have Status eq 'Open'
3. I use a filter array to remove the items that are past due.
   • The filter is @lessOrEquals(ticks(concat(item()?['Date_x0020_Due'], 'T00:00:00Z')), ticks(utcNow()))
     • Ticks() Converts a timestamp into the number of ticks since January 01 1601 (according to flow at least, the documentation seems to be off in this case).
     • Using this I convert my date into a timestamp by using concat() to add time to the date.
     • I compare this to the current UTC time and see if it's less that the current date.
4. I take the filter array and input it into an HTML table.
5. I send an email containing that html table.