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 current date and 2 years older without hardcoding the dates. Can someone tell me how to update my SQL statement to do this?

 

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

4 Replies

  • 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