SQL ora00907 error

Question

Every time I run this query, I'm getting ora-00907 error:

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

SELECT COUNT(*) 
  FROM jobseekerprofile
  JOIN userprofile on jobseekerprofile.portalprofileid = userprofile.portalprofileid
WHERE TO_CHAR((jobseekerprofile.createddate >= '23-APR-2013'),
      TO_CHAR(jobseekerprofile.createddate >='23-APR-2013 14:00:00')) 
  AND TO_CHAR((jobseekerprofile.createddate < '24-APR-2013'),
      TO_CHAR(jobseekerprofile.createddate < '24-APR-2013 13:59:00')) 
  AND userprofile.domainid = 1

Why are you trying two CreatedDate >= something conditions and two CreatedDate < something conditions? Even if you fix the parentheses you'll have lots of trouble with the dates - both with syntax and with logic. Which dates/times you want to include in the WHERE? — Ed Gibbs
i want to get the number of records created between 23/04/2013 14:00:00 and 24/04/2013 13:59:00 — Constantine
You can use the BETWEEN keyword. jobseekerprofile.createddate between to_date('23-04-2013 14:00:00','dd-mm-yyyy hh24:mi:ss') and to_date('24-04-2013 13:59:00','dd-mm-yyyy hh24:mi:ss') — Noel

APC APC · Accepted Answer · 2013-04-26T05:15:06

"i need the records to have date with timestamp"

Dates have time elements; there always taken into consideration when comparing two dates. So your second comparison will reject any value of 24-APR-2013 which occurs after midnight. So you definitely need to correct that.

You may have another problem because you are casting dates to strings. Strings have different comparison algorithms. Instead you should be casting strings to dates.

where createddate between to_date('23-APR-2013 14:00:00', 'DD-MON-YYYY HH24:MI:SS' 
                  and to_date('24-APR-2013 13:59:00', 'DD-MON-YYYY HH24:MI:SS')

Might as well use BETWEEN to simplify the statement.

SQL ora00907 error

1 Answers