SQL Statement Variable Date Range

[wasonj1]

Hi There,

I have a query that is using an SQL statement that is currently pulling data between 2018-07-01 and 2019-07-01. I want to update the SQL statement to be able to pull data between the current date and 2 years older without hardcoding the dates. Can someone tell me how to update my SQL statement to do this?

[VasTg]

@wasonj1 

Why don't you just substract the date from sysdate?

Whats your Database?

[wasonj1]

Hi @VasTg,

I have very little experience with SQL, could you should me what that line of code would look like? I tried a variety of Date() and Sysdate() functions but always got an error message. I do not know what the database is, though I could ask someone to find out if its important (what types are there?).

Thanks for you help! 

[VasTg · Accepted solution]

@wasonj1 

The SQL will be little bit different with Oracle  or SQL Server.

Here are the examples. Replace your WHERE clause/line.

ORACLE:
WHERE M.Response_Date BETWEEN TRUNC(SYSDATE) AND TRUNC(ADD_MONTHS(SYSDATE,-24),'MONTH')

SQL SERVER:
WHERE M.Response_Date BETWEEN CAST(GETDATE() as DATE) AND DATEADD(MONTH,-24,CAST(GETDATE() as DATE))

If this helps, mark it as a solution

Kudos are nice too

[wasonj1]

It was an SQL server, I had to swap the CAST and DATEADD calls but that worked perfectly. Thank you very much!