cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ocdc2008
Advocate II
Advocate II

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

Hello, the following code returns an error:


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

 

Annotation 2020-02-18 093117.png

 

The SQL data line from SSMS is below:

 
 

SQL Server - Line I'm trying to collectSQL 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?

8 REPLIES 8
Anonymous
Not applicable

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

@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
Not applicable

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.

@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
Not applicable

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
Not applicable

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?

@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
MVP

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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,932)