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
Dynamics 365 Field Service : Create task-specific canvas apps and custom pages
Field technicians are the backbone of any service-centric organization. But their success depends on having the right tools at their fingertip...
Automating UI Testing for Canvas Apps with Power Platform Test Engine
Canvas Apps in Power Platform provide a flexible way to build rich, custom user interfaces using a low-code approach. However, as these apps g...
User-Defined Function (UDF) in Power Apps Canvas App – EMI Calculator Example
In this blog you will know about User-Defined Function (UDF) in Power Apps Canvas App with a EMI Calculator Example Watch the video below or s...
🔓 Understanding the “Access app scope” Feature in Canvas App Components (Power Apps)
💡 Introduction In Power Apps Canvas Apps, components play a crucial role in building reusable UI and logic blocks. However, traditionally, co...
Canvas App | Easy way to use SVG icons with PowerIcons
In my previous articles, I explained how to use SVG icons in Canvas Apps by manually modifying the SVG content to make it compatible with Canv...