cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Re: Filtering on-prem SQL data source by date

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

Highlighted
Post Patron
Post Patron

Re: Filtering on-prem SQL data source by date

Bump

Highlighted
New Member

Re: Filtering on-prem SQL data source by date

Status says solved? But there is no solution!

 

Highlighted
Resolver II
Resolver II

Re: Filtering on-prem SQL data source by date

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.

 

 

 

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filtering on-prem SQL data source by date

Is this still the same? Are there any updates?
Highlighted
Resolver II
Resolver II

Re: Filtering on-prem SQL data source by date

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

Highlighted
Advocate IV
Advocate IV

Re: Filtering on-prem SQL data source by date

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.

 


 

Highlighted
Frequent Visitor

Re: Filtering on-prem SQL data source by date

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.

Highlighted
Advocate IV
Advocate IV

Re: Filtering on-prem SQL data source by date

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

Highlighted
Advocate I
Advocate I

Re: Filtering on-prem SQL data source by date

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

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 (3,487)