Filtering a Dataverse table with date

(0) Share

Report

Posted on by randomsky

103

I have a Dataverse table with a date only field called entryDate that is used in a canvas app gallery. In the gallery items property I use this:

Filter(tblLedgers,entryDate=Text(DatePickerCanvas2.SelectedDate,"yyyy-mm-dd"))
 
To my surprise this works but gives a warning saying: Incompatible types for comparison. These types can't be compared: DateTime, Text.
 
I was expecting this to work but it doesn't:
 
Filter(tblLedgers,entryDate=DatePickerCanvas2.SelectedDate)
 
and neither does:
 
Filter(tblLedgers,entryDate=DateValue(DatePickerCanvas2.SelectedDate))
 
the last two filter expressions don't give any warnings but simply don't return any records.
 
I even tried using another filter with entryDate >= DatePickerCanvas2.SelectedDate, entryDate < DateAdd( DatePickerCanvas2.SelectedDate, 1) with this showing some records but is not consistent.
 
I'm very confused. Anyone else experience this?

Categories:

General Questions

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Inogic 1,057 Super User 2025 Season 2 on at

Like (0)

Report

Filtering a Dataverse table with date

Hi,

You can try the following formula:

Filter(
tblLedgers,
DateValue(entryDate) = DatePickerCanvas2.SelectedDate
)

Using DateValue(entryDate) removes the time portion from the Dataverse DateTime value, ensuring that the comparison is done correctly as date vs. date.

Hope this helps!

Thanks!

Inogic

Was this reply helpful? Yes No
WarrenBelz 150,314 Most Valuable Professional on at

Like (0)

Report
Filtering a Dataverse table with date

Hi @randomsky,

My understanding is that Dataverse stores dates in UTC time, so your second exercise with adding a day is on the right track. You need to find the start of the day - try this OnChange of the Date Picker
UpdateContext( { varStart: DateAdd( Self.SelectedDate, TimeZoneOffset(Now()), TimeUnit.Minutes ) } ); UpdateContext( { varEnd: DateAdd( varStart, 24, TimeUnit.Hours ) } )

then test for the date being between these.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like♥.
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No