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
Integrate Your Custom Copilot into Your Canvas App in Power Apps
Microsoft Copilot Studio allows creators to build tailored Copilots powered by AI effortlessly. In a few simple steps, you can implement your ...
Dynamics 365 Embedded Canvas Apps in Model-Driven Power Apps
Introduction In an era where business evolves at lightning speed, leveraging the latest technology is crucial for success. Dynamics 365,…...
Use Copilot to quickly filter, sort, and search canvas app galleries with SQL Server
We are excited to announce that Power Apps application runtime users can now use Copilot to quickly filter, sort, and search the items in canv...
Canvas App | Simplified Display of SVG Images
In one of my previous articles : Display an SVG icon in Canvas App ,I explained how to display an SVG icon using an Image control. While this ...
All About Modern Themes for Canvas Apps
{Solved} How to clear a DatePicker control in Canvas apps
Hello everyone and welcome to my blog. In today’s blog, I will show how you can clear a DatePicker control in PowerApps. You might be wo...
Coauthoring Feature in the Canvas App
Microsoft facilitates the development of Canvas apps, enabling users to efficiently access and manage the information necessary for their busi...