Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Filter date as number - In database

jeneir
8 - Asteroid

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.

 

3 REPLIES 3
LordNeilLord
15 - Aurora

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 

DavidP
17 - Castor
17 - Castor

Can you do something similar to this?

 

DavidP_0-1594192780940.png

 

jeneir
8 - Asteroid

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')

 

Labels