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

Re: Filtering on-prem SQL data source by date

Bump

jkindred
Level: Power Up

Re: Filtering on-prem SQL data source by date

Status says solved? But there is no solution!

 

strategery
Level 8

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
PhilD
Level 8

Re: Filtering on-prem SQL data source by date

Is this still the same? Are there any updates?
strategery
Level 8

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

KMI-Keith
Level: Powered On

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.

 


 

crisby
Level: Powered On

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.

KMI-Keith
Level: Powered On

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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,686)