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

(1) Share

Report

Posted on by ocdc2008

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 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-p/6151

Why doesn't this work?

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
ocdc2008 64 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
ocdc2008 64 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
ocdc2008 64 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Daryl LaBar 156 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
tpampalone 13 on at

Like (0)

Report
Copy link

Link copied!

@Dlabar , appreciate your documentation. I solved this issue a while back by converting my date field (rescheduleDate) in an Azure SQL DB to datetimeoffset(0). This allowed me to run filter ranges by the week using a
ClearCollect function such as:
ClearCollect(colWeeklyEvents, Filter(coatingPlanner ,rescheduleDate > varFirstWeek And rescheduleDate <= DateAdd(varFirstWeek, 7, TimeUnit.Days)));

I have run into this issue again however, where the code just stopped working. Was wondering if you had any follow up to your blog post to see if this has been a recurring issue for users. The collection I have populates a calendar gallery and am getting "Error when trying to retrieve data from the network". No code has changed in the past 1.5 years of initially resolving this issue.
Help appreciated.

Was this reply helpful? Yes No
Daryl LaBar 156 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Unfortunately, this sounds like something that will need a MS support ticket created for it. If you haven't changed anything, it sounds still work.

Was this reply helpful? Yes No