Filter and Lookup not working with date values

(0) Share

Report

Posted on by patriciocarpio

Hello all,

I have an app whit a date picker. I use that value to try to filter a data source (comming from an Azure SQL database).

I use the following formula on a gallery:

Filter('[dbo].[Inspection]', Date=dpDate.SelectedDate)

and this on a label:

LookUp('[dbo].[Inspection]', Date=dpDate.SelectedDate, InspectionID)

I get not records on the first formula and nothing on the second formula. If I filterÂ for another fields, I get records.

If I change the first formula to:

Filter('[dbo].[Inspection]', Date<>dpDate.SelectedDate)

I get all records.

Any ideas? What is the date value passed to SQL?

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

v-micsh-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @patriciocarpio,

What is the data type for the date field under SQL Server table?

Currently Date type from SQL Server can't work with PowerApps (a known issue), if possible, please switch to DateTime type.

Besides, by using the equal sign, the workable around here is:

Filter('[dbo].[Inspection]', DateValue(text(Date))=dpDate.SelectedDate)

Here we use DateValue( Text() ) together to format the DateTime Type from SQL Server to make it in the same format as DatePicker in PowerApps.

Doing this would make the formula in delegation limitation, which then could only work with the first 500 records.

See the article below for delegation:

Delegation

Regards,

Michael

Was this reply helpful? Yes No
tbendall 4 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi Michael,
Following on from this issue, is the same fix intended to apply to performing the same function as a LookUp?

i.e.
LookUp('[dbo].[Inspection]',DateValue(Text(Date))=dpDate.SelectedDate,InspectionID)
If so, will it work if the field in the SQL server table is smalldatetime?

I have tried the following but to no avail
LookUp('[dbo].[tRoster]',DateValue(Text(srRosterDate))=DatePicker1.SelectedDate,srID)

Was this reply helpful? Yes No
MasterB 58 on at

Like
a
(0)

Report
Copy link

Link copied!

Please vote for proper implementation at:
https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Powerapps-SQL-Server-Connector-Search-Filter-on-DateTime-columns/idi-p/828086

Thanks!

Was this reply helpful? Yes No