Alteryx Designer Desktop Discussions

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

SQL Editor Between Dates

smorrell2022
5 - Atom

I pull data from a database using the input data option, in this option I wrote and SQL statement, and I have a date field (called Old_date) that is in the format of 'YYYYMMDD', I've been able to use Cast to now format as 'YYYY-MM-DD' (and I call this New_Date).

 

I now need help that in my SQL in the Where statement to that will pull date between a certain period of time.  I need the between dates to be between the current date and then going back 2 years.

 

So something that looks like this,   WHERE Cast(Old_Date as Date) Between TO_CHAR(CURRENT_DATE - INTERVAL '2 years', 'YYYY-MM-DD') AND TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')

 

But this is not working.  

 

Thank you in advance for your help.

 

Stephen Morrell

 

 

 

 

2 REPLIES 2
cjaneczko
13 - Pulsar

The proper syntax will be based on the type of database you are connecting to as they all have different date formats. Take for example the below is what I use to pull from an Oracle database. This gives me the last 24 months excluding the month we are in. 

 

where PSTDT >= Add_Months(Trunc(SysDate, 'month'), '-24') 
	and PSTDT < Trunc(SysDate, 'month') 

 

smorrell2022
5 - Atom

Yes, I did not know the syntax of "Add_month" and I modified it to "Add_YEARS" which worked perfectly.  

 

Thank you.

 

Labels
Top Solution Authors