Hi PowerUsers,
I am trying to filter an SQL table on the basis of date.
In SQL i have column named "AttendanceDate" with data type as Date.
When i try to filter data in powerapps on the basis of this column I am receiving this error.
"We cannot apply operator < to types DateTimeZone and Date" (Check Screenshot Below)
Is there a fix for this or any workaround.
I need this date comparison to work, its an essential part for my App.
Looking forward to quick response.
Best Regards,
Ali Nawaz
As you noticed, you cannot compare against Date columns in SQL directly. There is a workaround (described in https://docs.microsoft.com/en-us/connectors/sql/), where you can create a calculated column in your SQL table based on that date of type INT that represents the date value, something along the lines of
ALTER TABLE TV_AttendanceTable
ADD AttendanceDateAsInt AS YEAR(AttendanceDate) * 10000 + MONTH(AttendanceDate) * 100 + DAY(AttendanceDate)
In your app you can then update your expression to use that new column:
Sort(
Filter(
Filter('[dbo].[TV_AttendanceTable]', WorkerNumber = WorkerRecord.PERSONNELNUMBER),
AttendanceDateAsInt <= Year(DateValue(PPEndDate)) * 10000 + Month(DateValue(PPEndDate)) * 100 + Day(DateValue(PPEndDate)),
AttendanceDateAsInt >= Year(DateValue(PPStartDate)) * 10000 + Month(DateValue(PPStartDate)) * 100 + Day(DateValue(PPStartDate)),
PayCycleID = First(PayCycColl).PayCycleId),
AttendanceDateAsInt,
Descending)
Hope this helps!
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
User | Count |
---|---|
186 | |
53 | |
41 | |
36 | |
30 |
User | Count |
---|---|
240 | |
75 | |
71 | |
69 | |
65 |