How to filter on data for the last 5 years from when you run the WF

[CidneyB]

Hello - I am trying to only filter data from the last five years from whenever I run my WF. For example: If I run it today, I want the last 5 years, if I run next week, I only want the last five years. 

I am currently using a filter but I dont want to have to change it every time I run the WF. 

My dates are YYYYMMDD so I need help with trying to create a formula to go along with this date format.

In database and out of database examples would be greatly appreciated.

Thank you

[DataNath]

No problem @CidneyB - happy to help! Yep this is also possible, we can do just about any manipulation of dates to achieve things like this. For the standard tooling, we can do the following. I have left 2 expressions in the Filter - one of them is commented out (just remove the '//' at line starts if you want to use them) as the Between() function is new to the 2023.1 version of Alteryx so I didn't want to assume you had this. If not, the one I have in there as standard with the usual X >= Y and X <= Z approach will work fine:

Same approach in SQL - we just have to isolate the year part of today's date and create a date from that, we can then -5 to get the year start 5 years ago as well. The Between() function is native to SQL Server so fine to use:

CAST(CAST(CAST("Actual Date Format" AS INT) AS CHAR) AS DATE)

BETWEEN

DATEFROMPARTS(YEAR(GETDATE())-5,1,1)

AND

DATEFROMPARTS(YEAR(GETDATE()),1,1)

Which results in the same as the standard tools:

Have once again attached my flow with the DB connection removed. Hope this helps! :)