SQL Editor Between Dates

[smorrell2022]

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

[cjaneczko · Accepted solution]

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]

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

Thank you.