ODATA Filter Query for Date

[rich106]

Is there any way to set an ODATA filterquery to filter all records where some field is less than or equal to today's date? Tried searching around and couldn't find much on the topic.

[v-yuazh-msft]

Hi @ bangorkeith,

Do you want to get records which's specific column is less than or equal to today's date?

Where is the records saved in?

I would create a flow to get items of a sharepoint list which's "Start Date" column is less than or equal to today's date,

Please refer to screenshot below to create the flow:

The expression in the "Filter array" action as below:

@lessOrEquals(formatDateTime(item()?['Start_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))

The flow would run successfully to get all items which's "Start Date" column is less than or equal to today's date as screenshot below:

Regards,
Alice Zhang

[rich106]

Prematurely marked this as solved...

The data is stored in a SharePoint list, in a Date formatted column. The following error is returned when I test the flow:

InvalidTemplate. The execution of template action 'Filter_Join_Date_1' failed: The evaluation of 'query' action 'where' expression '@lessOrEquals(formatDateTime(item()?['Start_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))' failed: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'

A more thorough explanation:

I need to data from a sharepoint list with the following parameters:

• End Date (date column in sharepoint) is greater than or equals todays date
• Join Date (date column in sharepoint) is less than or equals todays date
• Title equals some value, say 'Chief of Staff'

I had been attempting this with an ODATA filter query directly in the "Get Items" action from SharePoint, but the workaround there was ineffective (checking for a default "End Date" value set far in the future; never was able to get it to filter based on "Join Date". I assume all three of these filters would need to be combined in one "Filter Array" action for this to work properly, but I'm unsure how to go about doing that. Syntax also seems to be an issue, as evidenced by the above quote.

Even if we got past those issues, would it be possible to pull the "Email" column from any item left and use it in assigning an Approval? I'm unclear if the "Filter Array" action actually filters and edits the array output of "Get Items", or if the Body is it's own filtered output and the input array is unchanged.

[v-yuazh-msft]

I'm unclear if the "Filter Array" action actually filters and edits the array output of "Get Items", or if the Body is it's own filtered output and the input array is unchanged.

Hi @ rich106,

The error message which your provided told that there are some item's "Start Date" column is null, the expression "formatDateTime()" couldn't process the null value.

Please make sure all columns which you would use with expression "formatDateTime()" wouldn't be null.

The "Filter Array" action actually filters( not edits)the array output of "Get Items", the Body is it's own filtered output and the input array is unchanged.

If you could make sure the "End Date","Join Date" column of all items is not null, you could refer to screenshot below to create the flow:

You could fill in expression in the Filter Array as below:

@and(equals(item()?['Title'], 'Chief of Staff'),and(greaterOrEquals(formatDateTime(item()?['End_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd')),lessOrEquals(formatDateTime(item()?['Join_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))))

The expression in the "Assign to" field of "Start an approval" as below:

item()?['Email']

The flow would run successfully as below:

If there would be some items' "End Date" or"Join Date" column are null,you could refer to screenshot below to create the flow:

You could fill in expression  in the Filter Array 2 as below:

@and(not(empty(item()?['End_x0020_Date'])),not(empty(item()?['Join_x0020_Date'])))

You could fill in expression in the Filter Array as below:

@and(equals(item()?['Title'], 'Chief of Staff'),and(greaterOrEquals(formatDateTime(item()?['End_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd')),lessOrEquals(formatDateTime(item()?['Join_x0020_Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))))

The expression in the "Assign to" field of "Start an approval" as below:

item()?['Email']

The flow would run successfully as below:

Regards,
Alice Zhang

[leyburn19]

This seems very complex.  I have a daily schedule that simply gets the result in the Get Items Filter

This gets all the records where my DateTo Column is less than yesterday.  The formul in the expression box is addDays(utcNow(),-1)

Note the formula is inside ''

In you case it would need to be simple   utcNow()   Need to be conscious that you may need to adjust for time zone if there is a need for accruacy