user15361238
user15361238

Reputation:

Invalid relational operator error on ORACLE SQL

I have a query like below. But when I run the query, I get an error. How can I write it corrrectly ?

SELECT * 
FROM   HR.JOBS J JOIN HR.JOB_HISTORY H ON J.JOB_ID=H.JOB_ID
                 JOIN HR.EMPLOYEES   E ON E.JOB_ID=H.JOB_ID
WHERE  START_DATE>=93-JAN-01 AND END DATE<=97-AUG-31;

I want to write the sql query showing the job title, department name, employee's full name and start date for all jobs that started on January 1, 1993 and ended on August 31, 1997.

Upvotes: 0

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

You need proper date constants, which uses the DATE keyword:

SELECT *
FROM HR.JOBS J JOIN
     HR.JOB_HISTORY H
     ON J.JOB_ID = H.JOB_ID JOIN
     HR.EMPLOYEES E
     ON E.JOB_ID = H.JOB_ID
WHERE START_DATE >= DATE '1993-01-01' AND
      END_DATE < DATE '1997-09-01';

Note that I changed the end date to be Sept 1 rather than Aug 31. This does what you intend whether or not the comparison column has a time component (which is allowed in Oracle).

Upvotes: 1

Related Questions