SQL Date type comparison in powerapps

[Anonymous]

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

[CarlosFigueira]

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!