ClearCollect(Filter(SQLDataSource,DateTime>DateTimeValue(DatePicker.SelectedDate))) Doesn't Work

[ocdc2008]

Hello, the following code returns an error:

ClearCollect(punchReport,Filter('[dbo].[Punch]',InTime>DateTimeValue("2020-01-01 00:00:00.001")));

The SQL data line from SSMS is below:

SQL Server - Line I'm trying to collect

I've tried different ways to represent the date in the formula. Not a single method works. The following thread represents the same issue from 2016 with multiple people in months following having the same issue:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filtering-on-prem-SQL-data-source-by-date/td...

Why doesn't this work?

[Anonymous]

Is it possible that you're using the punchReport somewhere else in your app with a different data scheme?

[ocdc2008]

@Anonymous 

No, this is entirely isolated to a testing screen.

Based on what I'm finding elsewhere in the community, PowerApps doesn't support the ability to use the < and > operators when using ClearCollect(Filter()) in reference datetime fields in a SQL server. The issue existed in 2016 from previous posts and there have been many comments since then up through mid 2019 that indicate the issue hasn't been addressed.

[Anonymous]

Ah, I see. I did a test on my end and I got the same result. Here's how I got around it.

ClearCollect(testCol,'[dbo].[SqlTable]');
ClearCollect(testCol,Filter(testCol,DateTimeColumn<Now()))

Collect the table, then filter it.

---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up.

[ocdc2008]

@Anonymous 

Unfortunately that isn't going to work. I need these instructions to be delegable. I'll be filtering through thousands of records over time.

[Anonymous]

Keep in mind your rows may get cut off depending on the size of your table and your row limit. You can sort the sql table if you need the newest records, since you're dealing with datetime.

[Anonymous]

My recommendation then, would be to use a SQL view or a stored procedure and get results via Power Automate. Would that be an option for you?

[ocdc2008]

@Anonymous 

Yes that would be an option.

I'm already using Automate to get around the fact that the "In" operator isn't delegable. I'd like to avoid having to use so many interfaces. I may just add a field to the SQL table that is in date format and filter using the = operator.

Not the most elegant solution, but it'll work.

[Dlabar]

Converting the DateTime Column in your SQL DB to DateTimeOffset resolves this issue.  I've detailed a blog post here on how to do so.