cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power Apps
Power Apps

Re: Filtering on-prem SQL data source by date

This is a bug in PowerApps, which I have just filed and dispatched to the correct area owners. We will address this as soon as possible. In the mean time, if you are blocked, please use one of the following workarounds:

 

1. If possible, use a DATETIMEOFFSET type for your field instead of DATETIME.

2. Force-inhibit the delegation of the query to the SQL back end, by using non-delegatable constructs. For example:

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

 

Keep in mind that #2 will run the query locally on up to 500 rows, though. If your data is larger than 500 rows, you will need to go the route of #1 for now until this problem is fixed.

 

I hope this helps.

 

Radu Gruian [MSFT] ** PowerApps Staff

View solution in original post

19 REPLIES 19
Highlighted
Power Apps
Power Apps

Re: Filtering on-prem SQL data source by date

The type of your DateField column in SQL is unclear. You are listing the values as 2105-01-01 00:00:00 and such, but are those text values or actual date/time values?

 

If the former, could you please try this instead:

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

 

Radu Gruian [MSFT] ** PowerApps Staff
Highlighted
Frequent Visitor

Re: Filtering on-prem SQL data source by date

Hi Radu,

 

Thanks for your response. Here is the table definition:

 

CREATE TABLE dbo.DateTable (Name varchar(10) PRIMARY KEY, DateField DATETIME)
INSERT INTO dbo.DateTable VALUES ( 'Item1', '2015-01-01')
INSERT INTO dbo.DateTable VALUES ( 'Item2', '2017-01-01')

 

As you see, the column is defined as DATETIME. So when I use the formula you suggested I get this error:

"Invalid argument type (DateTime). Expecting a Text value instead."

 

 

Highlighted
Power Apps
Power Apps

Re: Filtering on-prem SQL data source by date

This is a bug in PowerApps, which I have just filed and dispatched to the correct area owners. We will address this as soon as possible. In the mean time, if you are blocked, please use one of the following workarounds:

 

1. If possible, use a DATETIMEOFFSET type for your field instead of DATETIME.

2. Force-inhibit the delegation of the query to the SQL back end, by using non-delegatable constructs. For example:

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

 

Keep in mind that #2 will run the query locally on up to 500 rows, though. If your data is larger than 500 rows, you will need to go the route of #1 for now until this problem is fixed.

 

I hope this helps.

 

Radu Gruian [MSFT] ** PowerApps Staff

View solution in original post

Highlighted
Frequent Visitor

Re: Filtering on-prem SQL data source by date

Great, thank you. The dataset is large, so local processing is not an option. Will you post an update here once the bug has been fixed?

 

Thanks!

Highlighted
Power Apps
Power Apps

Re: Filtering on-prem SQL data source by date

Will do. Thanks for your patience!

Radu Gruian [MSFT] ** PowerApps Staff
Highlighted
Advocate I
Advocate I

Re: Filtering on-prem SQL data source by date

Is there a way to filter using a "Today" or "Now" variable in a Flow connecting to SQL Server on-prem?

Like "Orderdate ge Today()"

And what about a way to have it filter only dates greater than the last execution time of the Flow?

 

Thank you,

Roberto

Highlighted
New Member

Re: Filtering on-prem SQL data source by date

Another solution is to add your hours offset to the datetime values before being stored/used for query/filter. This will then equate back to the original date value only.

Example in Hong Kong (GMT+8) I add 8 hours to the datevalue.

Hope that also helps.

 

Looking forward to the update....

 

 

Highlighted
Regular Visitor

Re: Filtering on-prem SQL data source by date

Is there an update on this getting fixed?   Facing the same situation and it's been a year since mentioned.

Highlighted
Regular Visitor

Re: Filtering on-prem SQL data source by date

Is there any update on this issue?   We tried the DateTimeOffSet suggestion, but could not get that to work.   We also tried turning the datetime into a number and evaluating that way, but that also is proving challenging.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (4,684)