Answered

Filter by Date produce wrong result

(0) Share

Report

Posted on by tedqn77

127

This is a canvas app connecting to an Azure MS SQL database. The data field contains date/time. The filter is to get all records matching a specific date from DatePicker (even if I hard code a specific date in the Filter, still wrong result). Records contains date/time starting at 12:xx am of the filter date are omitted. Records starting at 12:xx am of the next date are included in the result.

For example, the search date is 5/21/2022. Records with date 5/22/2022 12: 05 am is returned, but record with date 5/21/2022 12:05 am is not. I suspect this has something to do with time zone.

Code:

Filter(Attendance,
checkIn >= DatePicker1.SelectedDate,
checkIn < DateAdd(DatePicker1.SelectedDate, 1, Days))

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

AhmedSalih 6,678 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hello, @tedqn77 , Try this:
Filter(AddColumns(Attendance, "checkInDateOnly", Text(checkIn, DateTimeFormat.ShortDate)), checkInDateOnly >= DatePicker1.SelectedDate, checkInDateOnly < DateAdd(DatePicker1.SelectedDate, 1, Days))

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

Was this reply helpful? Yes No
Verified answer

tedqn77 127 on at

Like (0)

Report
Copy link

Link copied!

Thank you for the idea of adding column with truncated date/time into date. I was able to use your syntax with modification to make it work. The newly added column is a text type so it complains incompatible type conversion so I have to convert it back to date. And since it's just a date I can directly compare with the DatePicker.SelectedDate

Filter(AddColumns(MealAttendance, "checkInDateOnly", Text(checkIn, DateTimeFormat.ShortDate)),
DateValue(checkInDateOnly) = DatePicker1.SelectedDate)

Was this reply helpful? Yes No