SQL Server Connector Get Rows doesn't support datatime in OData

Question

It was discovered during a 'Get rows (V2)' on the SQL Server connector doesn't support the following search criteria: "column1 gt 2025-09-05T00:00:00Z and column2 lt 2025-09-06T00:00:00Z". Is there an alternative and more importantly why would dates not be supported. Seems like a major gap in functionality. Using a stored procedure isn't desired as that requires another team to create and manage it.

Answer 1

Hello Christopher Fryett,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Yes, the SQL Server connector in Power Automate does not support full OData datetime filtering due to some reasons as you noticed and for the fact that SQL Server connector’s OData filter has limited support for datetime formats, especially with 2025-09-05T00:00:00Z. These limits differ across environments such as Azure SQL versus on-prem gateways, which makes datetime filters unreliable in Power Automate.

If you are on Azure SQL or cloud connections, the best fix is using Execute a SQL query (V2). Write a parameterized query with DATETIMEOFFSET or DATETIME2 parameters, for an example:

SELECT * FROM dbo.MyTable
WHERE column1 >= @startDate
AND column1 < @endDate;

This approach keeps filtering sargable, uses indexes, and handles time zones reliably. - https://learn.microsoft.com/en-us/connectors/sql/

When on-prem gateways prevent Execute SQL queries, the next best option is creating a persisted computed column for the date portion of your datetime and indexing it. Example:

ALTER TABLE dbo.MyTable
ADD DateOnly AS CAST(column1 AS date) PERSISTED;
CREATE NONCLUSTERED INDEX IX_MyTable_DateOnly ON dbo.MyTable(DateOnly);

You can then filter in Power Automate with:

DateOnly ge '2025-09-05' and DateOnly lt '2025-09-06'

• https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver17

Other alternatives exist but with trade-offs. A view can expose a date-only column for filtering but is static and still requires DB changes. Power Query transformations can help with more advanced filtering through gateways but add complexity. For small datasets, Get rows (V2) followed by a Filter array works, though it is inefficient. - https://community.powerplatform.com/forums/thread/details/?threadid=41f7ea00-ed00-f011-bae3-7c1e5248e2ba and https://cloudminded.blog/2021/02/02/getting-data-from-a-function-in-an-on-prem-sql-server-via-gateway-in-power-automate/

When OData filtering is unavoidable, you can fall back on functions like year(), month(), and day(). For example: year(column1) eq 2025 and month(column1) eq 9 and day(column1) eq 5

However, these make queries non-sargable and prevent SQL Server from using indexes, so they scale poorly. - https://www.brentozar.com/archive/2018/06/can-non-sargable-predicates-ever-seek/

So, use Execute a SQL query (V2) with parameters if supported, otherwise create a persisted computed column with index for date-only filtering. Avoid function-wrapping filters for large datasets. For small or temporary needs, post-retrieval filtering in Power Automate is acceptable.

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 2

Hi Christopher Fryett,

Thank you for posting your question in the Microsoft Q&A forum

The issue you are encountering with the SQL Server connector in Azure Logic Apps (or Power Automate) is a known limitation when using the Get rows (V2) action. The SQL Server connector does not natively support filtering on datetime columns using OData query parameters like gt (greater than) or lt (less than). This is because the SQL Server connector translates OData queries into SQL queries, and certain data types (like datetime) are not fully supported in this translation.

Here’s a breakdown of why this happens and some alternatives you can use to work around this limitation

Use a SQL Query in the Get rows (V2) Action

• Instead of relying on the Filter Query field, you can write a custom SQL query to retrieve the rows you need.
• Example

SELECT * FROM TableName
WHERE column1 > '2025-09-05T00:00:00' AND column2 < '2025-09-06T00:00:00'

• Steps:
  1. In the Get rows (V2) action, select the Advanced options.
  2. Use the SQL Query field to input your query.

Use a Stored Procedure

• If your organization allows it, you can create a stored procedure in the SQL database to handle the filtering logic.
• Example Stored Procedure

CREATE PROCEDURE GetFilteredRows
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
    SELECT * FROM TableName
    WHERE column1 > @StartDate AND column2 < @EndDate
END

Why Dates Are Not Supported in OData Queries

OData Query Translation Limitation:

• The SQL Server connector uses OData syntax to filter rows, but the translation of OData queries to SQL queries has limitations in how it handles certain data types, including datetime.
  • The connector may not properly handle the formatting of datetime values (e.g., 2025-09-05T00:00:00Z) in the query, leading to errors or unsupported behavior.
  Connector Design:

    - The `Get rows (V2)` action is designed for basic filtering and querying but is not intended to handle complex queries or advanced data types like `datetime` in OData filters.
    ```**Security and Performance**:
  
  

• Allowing complex queries directly in the connector could introduce performance issues or security risks, such as SQL injection.

Reference

sql server - Azure Logic App, SQL Get rows with DateTime comparison - Stack Overflow

SQL Server - Connectors | Microsoft Learn

I hope the provided answer is helpful,

Please "Up Vote" if the information helped you. This will help us and others in the community as well.

Thanks