Date filters do not work for SQL Server in PowerApps

Today I faced an issue to get the records in PowerApps from SQL database by applying date filters (From and To Dates). I spent so much time to retrieve the records by using the below command “ClearCollect(FilteredWorkReports,Filter(‘[SQL_Table_Name]’, ReportDate >= FromDate.SelectedDate && ReportDate <= ToDate.SelectedDate))“, but I didn’t get the expected result. The reason is simple “Direct date filters do not work for SQL Server” based on this link from MS. So, the solution for this issue is to create a calculated column for the date column in SQL table with Integer datatype. The formula for this calculated column is “YEAR([your_date_field]) * 10000 + MONTH([ your_date_field ]) * 100 + DAY([ your_date_field ])“. The data in your table will look like below with new field

CustomerReportDateReportDateAsInt
Cust 12019-05-0120190501
Cust 22018-11-0820181108
Cust 32015-12-1520151215
Cust 42018-06-0720180607

Finally, I applied the date filter with new column like below which is working like a charm 🙂 “ClearCollect(FilteredWorkReports,Filter(‘[SQL_Table_Name]’,ReportDateAsInt >= Value(Text(FromDate.SelectedDate,”[$-en-US]yyyymmdd”)) && ReportDateAsInt <= Value(Text(ToDate.SelectedDate,”[$-en-US]yyyymmdd”))))“.

I hope it helps…..