cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
df
Level: Powered On

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
PowerApps Staff rgruian
PowerApps Staff

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

18 REPLIES 18
PowerApps Staff rgruian
PowerApps Staff

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
df
Level: Powered On

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

 

 

PowerApps Staff rgruian
PowerApps Staff

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

df
Level: Powered On

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!

PowerApps Staff rgruian
PowerApps Staff

Re: Filtering on-prem SQL data source by date

Will do. Thanks for your patience!

Radu Gruian [MSFT] ** PowerApps Staff
francorg
Level: Powered On

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

LateNightDBA
Level: Power Up

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

 

 

ericrick
Level: Power Up

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.

claydevin
Level: Power Up

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
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 179 members 5,105 guests
Please welcome our newest community members: