Forum Discussion

wasonj1's avatar
wasonj1
Frequent Visitor
6 years ago
Solved

SQL Statement Variable Date Range

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 curre...
Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    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

VasTg's avatar
VasTg
Memorable Member
6 years ago

wasonj1 

 

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

Whats your Database?

 

wasonj1's avatar
wasonj1
Frequent Visitor
6 years ago

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's avatar
    VasTg
    Memorable Member
    6 years ago

    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's avatar
      wasonj1
      Frequent Visitor
      6 years ago

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