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.
Posts
