How to Filter Dates in Canvas Apps Using Greater Than/Less Than Operators

Defining the Problem
Recently I was attempting to filter an on-premise SQL table by a DateTime field using a “greater than” operator, and displaying the results in a Data Table control. When I applied the “greater than” condition to my filter, it would return 0 results. The crazy thing was I wasn’t seeing any errors. So I then turned on the Monitor tool and took a look at the response of the getRows request:
{
"duration": 1130.2,
"size": 494,
"status": 400,
"headers": {
"Cache-Control": "no-cache,no-store",
"Content-Length": 494,
"Content-Type": "application/json",
"Date": "Thu, 05 Jan 2023 13:36:12 GMT",
"expires": -1,
"pragma": "no-cache",
"strict-transport-security": "max-age=31536000; includeSubDomains",
"timing-allow-origin": "*",
"x-content-type-options": "nosniff",
"x-frame-options": "DENY",
"x-ms-apihub-cached-response": true,
"x-ms-apihub-obo": false,
"x-ms-connection-gateway-object-id": "c29ec50d-0050-4470-ac93-339c4b208626",
"x-ms-request-id": "e127bd54-0038-4c46-9a31-ce94547c226c",
"x-ms-user-agent": "PowerApps/3.22122.15 (Web AuthoringTool; AppName=f3d6b68b-f463-43a2-bb2b-b1ea9bd1a03b)",
"x-ms-client-request-id": "e127bd54-0038-4c46-9a31-ce94547c226c"
},
"body": {
"status": 400,
"message": "We cannot apply operator < to types DateTimeZone and DateTime.\r\n inner exception: We cannot apply operator < to types DateTimeZone and DateTime.\r\nclientRequestId: e127bd54-0038-4c46-9a31-ce94547c226c",
"error": {
"message": "We cannot apply operator < to types DateTimeZone and DateTime.\r\n inner exception: We cannot apply operator < to types DateTimeZone and DateTime."
},
"source": "sql-eus.azconn-eus-002.p.azurewebsites.net"
},
"responseType": "text"
}
Ah, Power Apps shows no error since it returned a 400 status, but the body contains the actual error: "We cannot apply operator < to types DateTimeZone and DateTime.\r\n inner exception: We cannot apply operator < to types DateTimeZone and DateTime.\r\nclientRequestId: e927bd54-0038-4c46-9a31-ce94547c226c". Apparently my DateTime column in SQL does not play well with Power App’s Date Time. After some googling I found some community posts as well:
- Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server Reply
- ClearCollect(Filter(SQLDataSource,DateTime>DateTimeValue(DatePicker.SelectedDate))) Doesn't Work
- Filtering on-prem SQL data source by date
- date filtering problem
- Help with sorting dates from SQL to Powerapps canvas
The Solution
The last community post above suggests that I should try the DateTimeOffset column type in SQL, and after another return to the googling I found a very similar issue described by Tim Leung, describing the same thing. Unfortunately no one documented how to do this, so here I am, documenting how to do it for you dear reader, as well as future me ! Please be warned, I’m still not sure how DateTimeOffset plays with other tools/systems, so test first!)
- Update the DateTime Column in SQL Server
- Refresh the Data source in the app
- Reload the app I had problems with the Data Table control I was using not applying the timezone offset correctly. Reloading the app seemed to fix this issue.
- Viola!
ALTER TABLE dbo.<YourTableName>
ALTER COLUMN <YourDateColumn> datetimeoffset(0) NOT NULL;
UPDATE dbo.<YourTableName>
SET <YourDateColumn> = CONVERT(datetime, <YourDateColumn>) AT TIME ZONE <YourTimeZone>;
/*
I don't believe there is a Daylight Saving Time option to timezones, but I just happened to be in EST, not EDT, so my last line looked like this:
SET <YourDateColumn> = CONVERT(datetime, <YourDateColumn>) AT TIME ZONE 'Eastern Standard Time';
Use SELECT * FROM Sys.time_zone_info to find your time zone.
*/
It’s not hard, but it definitely is a headache that I would hope Microsoft will solve.
Published on:
Learn moreRelated posts
How to Detect that a PCF is Running Inside a Canvas App / Custom Page
Usually my target is to develop a PCF working inside all types of apps: Canvas App (or Custom Pages), Model-Driven Apps and hopefully also ins...
Power Up Your Canvas Apps with No Code Interactive Maps: A Game-Changer for Sales and Field Teams!
If you’re a Sales Manager juggling territories, appointments, and customer data across multiple tools, it’s time to simplify things—with maps ...
Debug PCFs using a Browser Autoresponder Extension (MDA & Canvas Apps)
I’m sure the Autoresponder idea for Debugging PCFs (or WebResources) is not new to you. Debugging with an autoresponder solution is a li...
Summer 2025 Dynamics 365 Maps Release: Smarter Routing, Azure Maps, Canvas Apps & More!
Technology never stands still, and neither does Team Maplytics! With our latest March 2025 updates, your geo-mapping experience within Dynamic...
Building Streamlined, Traceable and Error Handled Canvas App Functions
Building processes in Canvas App is fast and you can get a lot done very quickly. Actions that, for example, a button control does, can become...
PowerPlatformTip 134 – ‘Optimize Canvas Apps with YAML’
Optimize your Power Apps with YAML! Copy Canvas App code and let AI suggest improvements for performance and structure. Boost app efficiency t...
[Power Apps] Creating a Full Outer Join Between Collections in Canvas App
Level of Difficulty: Intermediate – Senior. To join or not to join! Let’s delve deep into the realm of data manipulation by introd...