cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BrianHFASPS
Level 8

Date comparison inside Filter of SQL table fails but works outside of Filter?

I have a table in SQL that has two fields 'Date Start' and 'Date End' that are stored as date types. I have this code that seems to work:

First(Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID')).'Date Start' < AttendanceDate
First(Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID')).'Date End' > AttendanceDate

AttendanceDate is another SQL date field from a different table that has been put into a collection then used in a gallery. I want to refine my filter statement to include that check. I tried:

First(Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID', 'Date Start' < AttendanceDate, 'Date End' > AttendanceDate))

When I do this I get the following error message:

Filter Error.png

I know there are special requirements with PowerApps vs SQL and date fields. In other cases I have used Text versions and that works great for direct comparisons but I want to do ranges. Can anyone recommend a work arround?

3 REPLIES 3
BrianHFASPS
Level 8

Re: Date comparison inside Filter of SQL table fails but works outside of Filter?

I have found a workaround not sure if it is best so would welcome any comments. I started with collecting a narrow list:

Collect(StudentRoster,Filter('[dbo].[Activity Rosters]',DailyActUID = 'Bill Student List'.Selected.'User ID'))

Then I am able to do the LookUp in the way I wanted:

LookUp(StudentRoster,'Date Start' < AttendanceDate && 'Date End' > AttendanceDate, ActivitySignupID)

This may be optimum as the Collect filters it down to very few records and then I can locally in the app narrow to specific one in easiest way.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Date comparison inside Filter of SQL table fails but works outside of Filter?

Date comparisons with SQL tables currently have an issue in which the delegation doesn't work properly (see https://docs.microsoft.com/en-us/connectors/sql/ for information). To work around this, you can add a pair of calculated columns in your SQL table:

ALTER TABLE [Activity Rosters]
ADD [Date Start Int] AS YEAR([Date Start]) * 10000 + MONTH([Date Start]) * 100 + DAY([Date Start])

ALTER TABLE [Activity Rosters]
ADD [Date End Int] AS YEAR([Date End]) * 10000 + MONTH([Date End]) * 100 + DAY([Date End])

And you can compare the dates with the numeric value of the AttendanceData from your app:

First(
    Filter(
        '[dbo].[Activity Rosters]',
        DailyActUID = 'Bill Student List'.Selected.'User ID',
        'Date Start Int' < (10000 * Year(AttendanceDate) + 100 * Month(AttendanceDate) + Day(AttendanceDate)),
        'Date End Int' > (10000 * Year(AttendanceDate) + 100 * Month(AttendanceDate) + Day(AttendanceDate))))

Hope this helps!

BrianHFASPS
Level 8

Re: Date comparison inside Filter of SQL table fails but works outside of Filter?

Thanks for the idea, I will keep in that in my pocket in case I need it in the future. It is similar to the date to string in SQL for compare someone else showed me before. Since my Collection workaround requires no changes to SQL or any extra date formatting I will stick with it until the SQL connector is hopefully updated to support dates properly. I am filtering with my initial query from 1000 to 1-2 so that already reduces my data time / costs, then locally I can shrink to the specific one I want.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 4 members 5,520 guests
Recent signins:
Please welcome our newest community members: