Hi!
I have a date column stored as a number (yyyymmdd) and want to filter the last 6 months. My manually added filter: "TransactionDate" >= 20200201
How can I change this formula to be dynamic?
I would like to take the current date (or start of month), subtract 6 months and have that as a number.
Using the in-db filter tool.
Solved! Go to Solution.
Hey @jeneir
What database is it? This is very important when using InDB as you need to write the function in the language of the DB.
Presuming this is SQL you would probably need to first convert the date to an actual date:
Convert(Date, [Field], 112)
Then do a dateadd function:
[Field] >= DateAdd(month, -6, GetDate)
I'm not a SQL expert so that syntax may not be exactly correct but it would be something similar
Thanks for the quick reply!
Indeed, was assuming the wrong language.
With Netezza SQL I ended up with date->string->number: to_number(to_char(add_months(date_trunc('month', current_date), -6), 'YYYYMMDD'), '99999999')