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]

@superjesse 

This is most likely because the getdate() function returns date time in YYYY-mm-dd hh:mm:ss format. In order to get just the date wrap the getdate into trunc

trunc(getdate())

 See here for more detail.

I'd also check the format in which your dates are stored just in case.

[superjesse]

Thanks for the response. That didn't work, but my developers told me that our PostgreSQL dates are in the TIMESTAMPTZ format. Any familiarity with that?

[MichalM]

Could you run a select on flags.fault_date to see the format and post it here?

[superjesse]

The 'Type' is Date and the 'Size' is 10.

[MichalM]

What about an example value @superjesse ?

[superjesse]

2019-08-16

The strange thing is that syntax works on other databases even with this type of value. But for this AWS RDS it doesn't work on any table.

[MichalM]

I'm a bit confused as you previously suggested that the below syntax works on the DB 

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

Would the below work?

Where flags.fault_date > '2019-08-16'

[superjesse]

This works:

Select .faults_and_flags.process_fault_date From faults_and_flags Where faults_and_flags.process_fault_date > '08/15/2019'

This doesn't work:

Select faults_and_flags.process_fault_date From faults_and_flags Where faults_and_flags.process_fault_date > DateAdd(DAY, -7, GetDate())

Select faults_and_flags.process_fault_date From faults_and_flags Where faults_and_flags.process_fault_date > DateAdd(DAY, -7, trunc(GetDate()))

[superjesse]

Yes, this works.

Select faults_and_flags.process_fault_date From faults_and_flags Where faults_and_flags.process_fault_date > '2019-08-16'