Filtering on-prem SQL data source by date

[df]

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!

[Anonymous]

Has this been fixed yet?  I mean seriously? Cant filter a SQL data source using datetime inequalities?  And this was over a year ago..

[BrendonBrooksP1]

Bump

[jkindred]

Status says solved? But there is no solution!

[strategery]

I came here intending to find a solution for something else and this caught my eye. What I ended up doing myself to get around this was creating a report in PowerBI, "Pin Live Page" to create a new dashboard containing the full report, then inserting the dashboard into PowerApps using Insert>Controls>PowerBI tile. PowerBI gives you the ability to display the table and create a filter by date. Pop it into PowerApps and you're done, no formulas or anything.

I have another solution using only PowerApps that may work for some people. As an example I made the date filter work for picking specific dates and displaying ALL records matching the filter (filter returns only 500 by default). If this interests anyone, describe your table and the type of filtering you require and I will attempt to come up with a solution for you.

[PhilD]

Is this still the same? Are there any updates?

[strategery]

Appears to be the same, I had the misfortune of running into this today. Suggestion by @rgruian of using DateTimeOffset does work for me.

I couldn't change the table design but PowerApps works with Views now, so I created a new View and used CAST to add a 'datetimeoffset' field that i can now use to filter

[KMI-Keith]

Did this ever get fixed as Im still having this same issue now and I cant compare dates?

If I dont Filter() and just show the DB Source then both are showing as 5/2/2019 in the date picker field and the Browser Gallery label for the WorkDate field.

But for some reason I cant get this to work

Filter('[dbo].[TempAppGreenCardData]', Value(EmployeeIDSearch.Text)=EmployeeID, WorkDate=WorkDateSearch.SelectedDate)

If I do just 

Filter('[dbo].[TempAppGreenCardData]', Value(EmployeeIDSearch.Text)=EmployeeID)

it works fine.

Any help would be appreciated!

Keith

---

@rgruian wrote:

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.

---

---

@rgruian wrote:

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.

---

[crisby]

Hi, @rgruian can we have a response whether there will be a solution to this problem?

Not being able to filter on-premise sql by datetime seriously inhibits powerapps efficacy.

[KMI-Keith]

@crisby I found a workaround by making another column which is calculated in SQL and it works now.

Created column MyDateInt in SQL and made the calculated column:

((datepart(year,[DateToConvertField])*(10000)+datepart(month,[DateToConvertField])*(100))+datepart(day,[DateToConvertField]))

Then in the powerapps you create another Text Input field(FormatedDatePicker that I created) that is default to:

Value(Text(DatePicker.SelectedDate, "[$-en-US]yyyymmdd"))

This allows me to compare the Sql to the DatePicker field after its converted and Filter a Browse Gallery to the SQL items that match the date picked.

[ocdc2008]

I am having the same issue today in February 2020. This issue seems to have not been fixed. I just posted a new thread. The PowerApps Staff guy from this post hasn't posted since November 2016. I'd bet he left Microsoft.

My thread is at:

https://powerusers.microsoft.com/t5/Building-Power-Apps/ClearCollect-Filter-SQLDataSource-DateTime-g...