Power Automate actions to get rows for on-prem databases do not adhere to current ODATA specifications. Filtering on date and datetime data types does not work at the source. This is extremely problematic for large datasets and all time-series data, as users are unable to filter the dataset before records get sent to Power Automate. Documentation for the getrows actions implies that it follows an ODATA specification however documentation for on-prem connectors calls out date and datetime datatypes are not supported.
Workarounds suggested include the following:
Stored procedure as a table value function or similar to return filtered datasets.
In-effective for environments where access or capability to modify the database is limited.
Creates additional technical debt / objects within database that requires maintenance
May need to be adjusted for different types of data sets making it an unwieldly solution at scale
Collect all records and filter the dataset within the flow
Not scalable for large datasets. Built in default Power Automate query limits at 5,000 records means that any dataset with more than 5,000 records is ineligible for this method without altering settings / environment defaults. Altering default is not a good solution because it may allow other users/flows in the same environment to build flows that would be resource hogs and processing overly large datasets
Apply filter for each component, ie the filter "fieldDateTime <= 2021-10-20" could be written "year(fieldDateTime ) <= 2021 and month(fieldDateTime) <= 10 and day(fieldDateTime) <= 20"
This works however would have inconsistent / undesired results
Data from previous years would not include data from November or December
No previous months' data would include days after the 20th of the month
Per MS Support case TrackingID#2106070040003284 , this is not supported and is not on the roadmap to fix. The limitation is with how the code was developed for the mashup engine. The problem is the datetime datatype and the Odata version implemented with the mashup engine which is what is used to communicate with the gateway.
Note: if filtering on date only, there is a function 'date()' that can *sometimes* be utilized to convert the string within Power Automate which holds the date/datetime to a date datatype which DOES work. I have tested this and documented it in this post on the community page, however this function itself is not documented. Continuing functionality of this function is therefore not something to count on as far as I'm aware.