Unanswered

Trying to add a filter to a gallery based on datepicker from a sql datasource. The date format is YYYY-mm-dd.

(0) Share

Report

Posted on by ashajohn2021

When I add a filter to the a SQL datasource gallery and I add a datepicker to allow me to filter the records on date. SQL displays the date as YYYY-MM-dd. Datepicker is showing date as MM\dd\YYYY. When I enter a date that I know I have data for, no data is display. I don't get an error in the formula.

Formula for Gallery.items: Filter(vw_Expenses, Date = dp_ViewExpensesDateFilter.SelectedDate)

Nothing is displayed. Is it because SQL displays dates as YYYY-MM-dd? If so, how do I fix this? If not, what is the recommended change to get the filter working on my gallery?

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

MVP-Phipps 3,508 Super User 2024 Season 1 on at

Like (0)

Report

You can format the selected date and then this will be an exact match to your records in SQL.

Look at DateTimeValue() function within your filter.

Was this reply helpful? Yes No
ashajohn2021 46 on at

Like (0)

Report

I added both Datetimevalue() and datevalue, nothing changed: Filter(vw_Expenses,Date = DateValue(dp_ViewExpensesDateFilter.SelectedDate,"yyyy-mm-dd"))

I tried adding Text in front of the formula, but I got the Incompatible type message.

Any suggestions?

Was this reply helpful? Yes No
ashajohn2021 46 on at

Like (0)

Report

BTW, This is a shortdate field not a datetime.

Was this reply helpful? Yes No
ashajohn2021 46 on at

Like (0)

Report

I found this solution in a different post. I got results this time, but the filter is not working. Anyone see where the problem is? Filter(vw_Expenses,DateValue(Text(Date, "en-US")) = DateValue(Text(dp_ViewExpensesDateFilter.SelectedDate, "en-US")))

Here is where I got this from: https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-using-date-field-from-SQL-Server/td-p/149691

Was this reply helpful? Yes No
MVP-Phipps 3,508 Super User 2024 Season 1 on at

Like (0)

Report

Can you add the two dates to a label and show me the output?

Was this reply helpful? Yes No
MVP-Phipps 3,508 Super User 2024 Season 1 on at

Like (0)

Report

Can you show me what you mean when you say you get results but the filter isn't working?

Was this reply helpful? Yes No
ashajohn2021 46 on at

Like (0)

Report

Here is the filter

When I run the app, I enter a date and you can see the filter was not applied:

Here is a print screen with the datepicker value on the top and the date field in the gallery. I see the problem is in the formatting. Can you help me fix? Not sure what to do. Thank you!

Was this reply helpful? Yes No
ashajohn2021 46 on at

Like (0)

Report

Was this reply helpful? Yes No
ashajohn2021 46 on at

Like (0)

Report

I didn't set datetime in SQL on the field. I'm not sure why it's displaying as datetime in the gallery. It was always have a time of 12:00 am because it's just a date field in SQL.

Was this reply helpful? Yes No
MVP-Phipps 3,508 Super User 2024 Season 1 on at

Like (0)

Report

Is your date a date only or a date time?

Was this reply helpful? Yes No