5 Replies Latest reply: Feb 6, 2011 4:40 PM by William Robertson RSS

    date_sub() giving error

    user540571
      Hi, I am trying to run the following sql , i am getting
      Here is my sql

      select * from_achvmt where achvmt_dt >= DATE_SUB(curdate(),INTERVAL 15 DAY);

      ORA-00907: missing right parenthesis
      00907. 00000 - "missing right parenthesis"
      *Cause:   
      *Action:
      Error at Line: 1 Column: 89


      why i am getting this error. My oracle version is 11g. I want to pick the data from table from last 15 days. whats wrong in my query
        • 1. Re: date_sub() giving error
          Frank Kulash
          Hi,
          user540571 wrote:
          Hi, I am trying to run the following sql , i am getting
          Here is my sql

          select * from_achvmt where achvmt_dt >= DATE_SUB(curdate(),INTERVAL 15 DAY);

          ORA-00907: missing right parenthesis
          00907. 00000 - "missing right parenthesis"
          *Cause:   
          *Action:
          Error at Line: 1 Column: 89


          why i am getting this error. My oracle version is 11g. I want to pick the data from table from last 15 days. whats wrong in my query
          Neither DATE_SUB nor CURDATE are built-in oracle functions.
          In Oracle, SYSDATE returns the current DATE (from the database server), and you can add or subtract a number of days from that DATE by just adding or subrtacting a NUMBER, so you may want something like this:
          SELECT  *
          FROM    achvmt          -- space (not _ ) between FROM and table name
          WHERE   achvmt_dt  >= SYSDATE - 15
          ;
          This includes future DATEs, as well as the last 15 days.

          You can add or subtract an INTERVAL DAY TO SECOND ro or from a DATE, if you want to.
          In INTERVAL DAY TO SECOND literals, the number is quoted, so the correct syntax is:
          SELECT  *
          FROM    achvmt
          WHERE   achvmt_dt  >= SYSDATE - INTERVAL '15' DAY
          ;
          • 2. Re: date_sub() giving error
            Etbin
            Not 11g yet but something like below used to be good enough for 10g
            select * 
              from_achvmt 
             where achvmt between trunc(sysdate) - 15 and trunc(sysdate) + 1 - 1/24/60/60
            Regards

            Etbin
            • 3. Re: date_sub() giving error
              Solomon Yakobson
              Well, where did you dig that query from??? Did you open Oracle SQL Reference manual at least once? There is no such thing as currdate() in Oracle. Current date is sysdate. And it is current date and time. SO trunc(sysdate) will give you start of current day. Interval requires quoted value and what is DATE_SUB? To subtract interval or number of days you simply use minus:
              SQL> alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss'
                2  /
              
              Session altered.
              
              SQL> select trunc(sysdate) - 15 from dual
                2  /
              
              TRUNC(SYSDATE)-15
              -------------------
              01/22/2011 00:00:00
              
              SQL> select trunc(sysdate) - interval '15' day from dual;
              
              TRUNC(SYSDATE)-INTE
              -------------------
              01/22/2011 00:00:00
              
              SQL> 
              SY.
              • 4. Re: date_sub() giving error
                William Robertson
                Looks like MySQL: http://www.w3schools.com/sql/func_date_sub.asp

                In Oracle, the first argument to an INTERVAL literal must be a quoted string. Crazy but true.
                • 5. Re: date_sub() giving error
                  user540571
                  Thanks for the replies. My problem fixed.