Skip to main content
leeaan0001
Inspiring
June 26, 2021
Solved

How can we filter based on only month and day using queryFilter?

Hello,

The data in my database is of form yyyy-mm-dd. While filtering data, I want to ignore the year. How can I do it?

Thanks.

stefanhelzle0001
stefanhelzle0001Answer
Brainy
June 26, 2021

That is not possible out of the box. I suggest to implement a view in database which puts day, month and year into separate columns. Then you can filter on this. In case your data volume is large, consider performance tests.

View in thread

4 replies

stefanhelzle0001
stefanhelzle0001Answer
Brainy
June 26, 2021

That is not possible out of the box. I suggest to implement a view in database which puts day, month and year into separate columns. Then you can filter on this. In case your data volume is large, consider performance tests.

    leeaan0001Author
    Inspiring
    June 27, 2021

    Thank you so much for the advice.

    csteward
    csteward
    Brainy
    June 28, 2021

    Note if the database side VIEW method is utilized on a datetime value, any DB-side calculations to return DAY, MONTH and YEAR separately will be done on those GMT values and may be incorrect depending on the time of day in the value, as Appian's time zone conversions are bypassed with this method.  To resolve this, we implemented the solution from mssqltips.com to convert GMT, with daylight savings, and it works great as we always report in Eastern time.  The datetime values are wrapped in that function call, within SQL's DATEPART() function.

    Terms & ConditionsAccessibility statement