Reputation:
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
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