cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

SQL Date type comparison in powerapps

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.
Screenshot_8.png


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)  


Screenshot_1.png

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

1 REPLY 1
CarlosFigueira
Power Apps
Power Apps

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!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,469)