Loading...

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

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:


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!)

  1. Update the DateTime Column in SQL Server
  2. 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.
    */

  3. Refresh the Data source in the app

  4. In Canvas Apps Studio, click data source options menu and select Refresh
  5. Reload the app
  6. 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.

  7. Viola!


It’s not hard, but it definitely is a headache that I would hope Microsoft will solve.



Published on:

Learn more
.Net Dust
.Net Dust

NULL

Share post:

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

10 days ago

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

12 days ago

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

17 days ago

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

17 days ago

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

18 days ago

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

23 days ago

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

24 days ago

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

1 month ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy