Solved: Filter Date in SQL using DatePicker in PowerApps

Raif8522 · ‎05-30-2022

Hi,

i am trying to filter SQL DB based on DatePicker on PowerApps. The Format of the Date is SQL Date is yyyy-mm-dd. When i try Below Formula it returns nothing

Filter(SQLDB,DateOfBirth=DatePicker3.SelectedDate)

i changed the formula a bit and this time i only get 2000 records

Filter(AddColumns(SQLDB,"datecon",Year(DateOfBirth) * 10000 + Month(DateOfBirth) * 100 + Day(DateOfBirth)),datecon=value(Year(DatePicker3.SelectedDate) * 10000 + Month(DatePicker3.SelectedDate) * 100 + Day(DatePicker3.SelectedDate))

DB is PDW and literally i cannot modify/update it.

is there still any workound to be able to filter SQLDB by date?

SQLDB has over 2mil records

Thanks

CarlosFigueira · ‎05-30-2022

Filtering by dates on a SQL Server table should just work. One possible issue that I can see is that, if the 'Date time zone' property of the date picker is set to UTC or Local. Unless you know exactly what you are doing, the value should be set to Local, as this is the mode where apps work in Power Apps - see the screen capture below.

If this is not your issue, another alternative you can try to understand what is going on is to open the Monitor, and see what the app is sending to the server. To open the monitor, you can go to the Advanced Tools option as shown below:

Once the monitor is opened, and you see a message that it is connected, you can go back to the tab with your application and change the value of the date picker, to see what request is sent to the database. In the app shown above, I chose another date that has records in the table (June 17, 1985), and I can see some entries in the monitor:

The entry in the 'Network' category, in the 'getRows' operation is the one where the app is trying to fetch data from the server. Clicking in the row will show more details, including the request:

In this case, the request has the same date that I selected (1985-06-17)... That's what is supposed to happen - please let us know if this is not the case (i.e., you see a different date, or you don't see a network request when you change the date in the date picker).

View solution in original post

Drrickryp · ‎05-30-2022

@Raif8522

AddColumns() is not delegatable. The problem is not with Filter() which is.

Raif8522 · ‎05-30-2022

Unfortunately, i can only Read from DB. I don't think create/merge any columns there 😕

is there anyway to create a Date(Same format as in SQLDB) from string and pass it to the Filter as a Date, not as a text?

Drrickryp · ‎05-30-2022

@Raif8522

Can you rewrite your formula without the AddColumns()?

Raif8522 · ‎05-30-2022

@Drrickryp

if you meant like below, yeah but it gives delegation warning.

Filter(SQLDB,Year(DateOfBirth) * 10000 + Month(DateOfBirth) * 100 + Day(DateOfBirth)=Year(DatePicker3.SelectedDate) * 10000 + Month(DatePicker3.SelectedDate) * 100 + Day(DatePicker3.SelectedDate))

Drrickryp · ‎05-30-2022

@Raif8522

Try

Filter(
        SQLDB,DateOfBirth=DateValue(
                                    DatePicker3.SelectedDate,"en-US"
                            )
)
Or try
Filter(
        SQLDB,DateOfBirth=DateTimeValue(
                                    DatePicker3.SelectedDate,"en-US"
                            )
)

It depends on how the DateOfBirth column is formatted in SQL. Since I don't use SQL, I tried it on a SharePoint column in a 5000 item list and it worked for the second formula

Raif8522 · ‎05-30-2022

No Luck @Drrickryp Below is the DateOfBirth column from SQL. it is a Date column but format is different. Your formula doesn't give any errors nor returns any customers.

Drrickryp · ‎05-30-2022

@Raif8522

Filter(
        SQLDB,DateOfBirth=Text(DateValue(
                                    DatePicker3.SelectedDate,"yyyy-mm-dd"
                            )
)

Raif8522 · ‎05-30-2022

@Drrickryp

"Incompatible types of comparison. These types can't be compared:Date,Text"...

Even following formulas doesn't work

Filter(
        SQLDB,DateOfBirth=DateValue(Text(DateValue(
                                    DatePicker3.SelectedDate,"yyyy-mm-dd"
                            )
),"en-GB"))

or

Filter(
        SQLDB,Text(DateOfBirth)=Text(DateValue(
                                    DatePicker3.SelectedDate,"yyyy-mm-dd"
                            )
))

Stucked on that...