Trying to filter or lookup on a date field

[Spreck76]

Okay, been trying a few different solutions I've found on the boards but nothing seems to work. My app is pulling data from an Azure SQL database with a "date" data type. I have entered data using a DatePicker in PowerApps. I am trying to do a simple filter of said data based on a date chosen from a DatePicker. Essentially it is the same DatePicker used to enter the data I want to use to retrieve stored data. I have the Date time zone of the DatePicker set to Local.

When I use a simple label with the code DatePicker1.SelectedDate I see the correct date. When I use a filter on a Gallery for example using the formula:

Filter('[dbo].[GSP_DailyPrices]', Load_Date = DatePicker1.SelectedDate)

I get a blank gallery, no errors, no warnings. Not sure what I'm doing wrong here.

Appreciate the help.

Thanks,

[aprildunnam]

I would check the Load Date field and see what format it is expecting.  For example, if it's expecting Long Date but your Date Picker in PowerApps is a Short Date it might not work.  I would suggest taking a look at the Date Formatting options here:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/show-text-dates-times

[Appsessed]

Have you tried using LookUp instead of Filter?

LookUp('[dbo].[GSP_DailyPrices]', Load_Date = DatePicker1.SelectedDate)

[Meneghino]

Yes, definitely a bug!  Should be reported.  This functionalityused to work, but now seems broken.

Even this type of expression does not work now:

Filter('[dbo].[GSP_DailyPrices]', Load_Date = Date(2018,7,11))

Maybe @CarlosFigueira can kindly report this.

[Spreck76]

Yeah, I confirmed on the SQL server the data type is "date" so it is expecting short date only. I'll look at the link as well, thanks.

[Spreck76]

I don't think using a LookUp wouldn't work as it will only return the first record it finds. I need to filter a table based on the date but to return multiple records. Appreciate the idea though. 

[Spreck76]

You are correct, tried and while a call is made (I see the squares racing across the top of the screen) my gallery remains blank. No errors, no warnings, nothing. This is frustrating, seems like a pretty basic and straighforward thing to do. Oh well, hopefully it will get resolved soon. I'll keep plugging away at the rest of my app in the meantime.

Thanks for the help.

[Meneghino · Accepted solution]

The obvious work-around which works 100% of the time is to add a calculated column to your table (and add that column to an index in order to safeguard performance) containing an int type of value yyyymmdd taking the values from your date column.

Then use that column for any filtering in PowerApps.

Once the issues are resolved in PowerApps you just drop that calculated column.

[Spreck76]

Yep, worked like a charm. Adjusted my SQL table and PowerApp code to accomodate.

Thanks,

[Meneghino]

Here is a somewhat old post that still has partial value:

https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/