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
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...
How to integrate both Dataverse Business Process Flow Directly without the use of an custom API into Canvas App Component Design via SPACEKAT.
What this Article will Cover: Component Design! Component Design! Output Properties/ Are they Useful? Patch/Lookup and Delegation Dataverse Bu...
Maps for Dynamics 365 gets a Major Upgrade: Azure Maps, Canvas Apps & Smarter Territories!
Hey there, map enthusiasts and data wranglers! Ever felt like your business data is trapped in boring spreadsheets and CRM records? Like, you&...
Simple Event Session Check In Canvas App
The Customer Insights – Journeys (or Marketing App) has an option to create a new Check-in record, scan a QR code and check someone in t...
How to use custom sorting in canvas apps
Records can be easily sorted in an ascending or descending order. Learn how to implement a complex custom sorting in canvas apps! The post How...