Answered

Filter Date in SQL using DatePicker in PowerApps

(0) Share

Report

Posted on by Raif8522

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

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Raif8522

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

Was this reply helpful? Yes No
Raif8522 19 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Raif8522

Can you rewrite your formula without the AddColumns()?

Was this reply helpful? Yes No
Raif8522 19 on at

Like
a
(0)

Report
Copy link

Link copied!
@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))

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
Raif8522 19 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Raif8522

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

Was this reply helpful? Yes No
Raif8522 19 on at

Like
a
(0)

Report
Copy link

Link copied!
@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...

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Raif8522

I'm at a loss. Time to see if @CarlosFigueira has an answer.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Raif8522

I'm at a loss. Time to see if @CarlosFigueira has an answer.

Was this reply helpful? Yes No