cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
df
Frequent Visitor

Filtering on-prem SQL data source by date

Consider the following table residing in an on-premise SQL database called DateTable (pulled into powerapps via an on-premise gateway):

 

Name   DateField

-------   ---------------------------

Item1    2015-01-01 00:00:00

Item2    2017-01-01 00:00:00

 

When I try to filter this table in Powerapps using the following statement, nothing comes back.

Filter('[dbo].[DateTable]', DateField > DateValue("1/1/2016"))

 

To debug the issue, I created the following collection:

ClearCollect(DateCollection, {Name: "Item1", DateField: DateValue("1/1/2015")}, {Name: "Item2", DateField: DateValue("1/1/2017")})

 

The same filter expression applied to this collection works and correctly brings back the one row for 2017:

Filter(DateCollection, DateField > DateValue("1/1/2016"))

 

Any idea why I can't filter my SQL data source by date?

 

Thanks!

21 REPLIES 21
LZars
Regular Visitor

This issue is unbelievable, I do not understand.

 

As a work around I have added a calculated column to represent the datetime as a integer.

 

[TimeInt] AS (datediff(second,'1970-01-01 00:00:00.000',[Time])),

Yeah that was the exact workaround I came up with too. I should have edited my comment to represent this.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,576)