Filter an SQL Date column

[sajarac]

Hi there,

I am trying to filter my gallery against a SQL datetime column. The format of that column is like this i.e 1/25/2022 1:25 PM. and the value to be compared is i.e ThisItem.value = 1/25/2022       

I guess the error here is because the values are not the same. I don't need the time I only need to filter the nested gallery and getting the value without any delegation issue. 

Any help please?

thanks in advance

[Pstork1]

You can't filter directly on dates in SQL without delegation warnings.  Here's the footnote from the documentation with the workaround

SQL Server - Connectors | Microsoft Docs

1. Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

[sajarac]

😞

Thank you very much for your prompt response. I am trying to build a calendar but I don't have access to the SQL table to modify al alter the table. Is there any workaround that I can do it in PA?

[Pstork1]

There is no way to do it without the delegation warning.  I usually recommend using multiple filters in that case with inner filters that are delegable and an out filter that is non-delegable.  That way as long as the inner filter gets you below the data row limit the delegation warning doesn't mean anything.

[sajarac]

OK, sounds like we have some hope.

Now saying that I can filter my dbo based on Status First and then if the dbo Date column = ThisItem.Value. Is this what you mean?

thanks in advance

[sajarac]

Never mind @Pstork1 , I guess the date is the key value to be filtered. So it looks like there is no solution without delegation warning.

Now would you mind to explain how to do it regardless of the delegation?

[Pstork1 · Accepted solution]

If its not a large database and you can live within the delegation limits then all you need to do is modify the field and target to have the same format.  But I'm not sure what you are filtering using ThisItem?  ThisItem only exists inside a gallery, so you can't use it to filter the items in a gallery.  But lets say I have a datetime column in SQL called StartDate.  This would be the filter

Filter(SQLDataSource, Text(StartDate,ShortDate) = "01/25/2022")

You can replace the literal in the formula with a variable or other string representation of the target date.

[sajarac]

Thank you very much !!!!

🙂

[PhoenixPubInc]

Hi @sajarac,

Did you manage to solved this? We have similar issue. I'm also trying to build a dynamic calendar against SQL server table. I managed to get it work by converting the two dates to shortdate. Its weird but it worked 😂

FIlter(datasource, Text(sqldatetime, ShortDate) = Text(ThisItem.Value, ShortDate))

I hoped it'll able to help you.

[sajarac]

Hey @PhoenixPubInc. thank you very much. I have the solution partially working. I still getting a delegation warning in my dynamic calendar. But we are ok with the implementation.

Thank you very much.