Filter Date in SQL by Today with calculated column

(0) Share

Report

Posted on by wstone

A few things I don't really understand.

1.) My formula works on Database Mart SQL 2017 in the cloud

2.) My formula works on Azure SQL database in the cloud

3.) My formula doesn't work on SQL Server 2022 on prem

I read and understand Direct Date Filters Don't Work on SQL Server Tables, why does it work in Azure SQL and Database Mart then? No one can answer this so far that I can find so moving on to the actual question below.

The Formula:

SortByColumns(Search(Filter([@'dailyLog'],DateAsInt=Today()), TextSearchBox1.Text, Address,Twp,Contractor,Permit,Type,Inspector), "Order", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

I did the "ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date]))" and now end up with 20240515 for 2024-05-15. How do I get my formula above to filter by today with the DateAsInt column? I see post after post saying you need to do the above and sort based on the DateAsInt column but no one tells you how to do that.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

wstone 16 on at

Like (0)

Report
Copy link

Link copied!
I've got this but it doesn't work either....I get "incompatible types for comparison. these types can't be compared number, text" What is the DateAsInt considered since its a calculated column? I can't get the date to format as "yyyymmdd"without the Text wrapper.

SortByColumns(Search(Filter(dailyLog, DateAsInt=Text(Today(),"yyyymmdd")), TextSearchBox1.Text, Address,Twp,Contractor,Permit,Type,Inspector), "Order", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @wstone ,

This is because DateAsInt column is of type number and Text function returns text value, they cannot be compared. Please add a Value function to include the Text function:

SortByColumns(Search(Filter(dailyLog, DateAsInt= Value(Text(Today(),"yyyymmdd"))), TextSearchBox1.Text, Address,Twp,Contractor,Permit,Type,Inspector), "Order", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

Best regards,

Was this reply helpful? Yes No