Alteryx Date Query to Data in Athena

Question

Hello Everyone,

I am using the query below and Athena does not like it.

where captured_date (between to_number(dateadd(month,-3getdate()),'99999999') and to_number(dateadd(month,-1,getdate()),'99999999'))

I was expecting the last three months of data to be returned.

The error that Alteryx returned is:

Error Message: line 17:22: mismatched input 'between'. Expecting: <expression>

danilang · Answer

Hi @Fescobar

It looks like you're missing a comma between the -3 and getdate() in your first dateadd function

where captured_date (between to_number(dateadd(month,-3getdate()),'99999999') and to_number(dateadd(month,-1,getdate()),'99999999'))

Dan

JarekSkudrzyk · Answer

@Fescobar 
apologies for not making myself clear.

I meant that according to the error message the issue is in 22nd character of the line, which is where "(between..." starts.
I think this opening bracket should be removed

Please try this query:
where captured_date between to_number(dateadd(month,-3getdate()),'99999999') and to_number(dateadd(month,-1,getdate()),'99999999'))

Fescobar · Answer

Thank you for replying .@JarekSkudrzyk

I am not sure that I follow what you're stating?  Do you mean that I need to add " (double quotations) somewhere?

Thank you,

Fausto