This content has been marked as final. Show 5 replies
user540571 wrote:Neither DATE_SUB nor CURDATE are built-in oracle functions.
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"
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
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:
This includes future DATEs, as well as the last 15 days.
SELECT * FROM achvmt -- space (not _ ) between FROM and table name WHERE achvmt_dt >= SYSDATE - 15 ;
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 ;
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>