0

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.

Improve this question
4
  • where start_date>'19921231'and end_date<'19970901' Commented Mar 9, 2021 at 13:35
  • select * is generally a poor practice - and in this particular case, you said you want to select a small number of specific columns from each table. Get in the habit of listing exactly what columns you want in the output in the SELECT clause. Commented Mar 9, 2021 at 13:39
  • Check this docs article about dates and this one about texts. Oracle does not know if your text is text until you explicitly specify it in appropriate way: using single quotes 'some text'. Commented Mar 9, 2021 at 13:53
  • 1
    "I get an error." But you didn't show the error, forcing others to make a guess. The points they make about handling of dates is valid, but I also see a syntax error ("AND END DATE" should be "AND END_DATE"). We don't know what error you are reporting. And another observation on dates -- Does the term "Y2K problem" ring any bells? You should ALWAYS designate 4-digit years, not 2-digit years. Commented Mar 9, 2021 at 14:33

1 Answer 1

Reset to default
1

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.