Input Tool Date Parameter for AWS RDS

[superjesse]

Hello there. I'm trying to add syntax to my input tool to limit the past 7 days of data in the input tool. It works with all of my inputs except for AWS RDS. This is the syntax that I was hoping would work. I've tried all different iterations and cannot get it to work correctly.

Where flags.fault_date > dateadd(day,-7, getdate()) 

This syntax works:

Where flags.fault_date > '08/01/2019'

I just want to only input the past 7 days worth of data for my workflow, and not sure why the syntax won't work on AWS RDS.

Any help thank you very much.

[MichalM]

What does the below return?

select dateadd(day,30,getdate());

[superjesse]

I'm putting your criteria here. It just errors out.

I tried that same syntax you sent in another database and it works. I just can't figure out what is the difference with RDS.

[MichalM · Accepted solution]

Can you just try the standalone select below outside of Alteryx?

select dateadd(day,-7,getdate());

[superjesse]

Ok, I will work my developers and get back to you. I'm currently only linked up via Alteryx.

[superjesse]

First I want to say thanks for helping me out. I researched this more and I discovered the syntax that finally worked. Its syntax for Postgres.

CURRENT_TIMESTAMP - interval '7 days'

[MichalM · Accepted solution]

@superjesse 

I've done some testing on Redshift and the below should work. 

where flags.fault_date > trunc(dateadd(day,-7,getdate()))

Like I mentioned above, the getdate() function returns date including time so we need to strip it of time element using the trunc function.