What is the alternate of Datediff() to find the date difference?

Question

I am trying to write a query which returns the list of students who have not enrolled in any class for more than 5 months. I think I have got the logic right but I am unable to use Datediff() in SQL Plus. I was wondering if there is any other way to it.

Query:

SELECT s.Reg_No, s.First_Name, s.Last_Name, s.Admission_Date
FROM Student s
INNER JOIN Enrolled_In e ON s.Reg_No = e.Reg_No
WHERE (s.Reg_No != e.Reg_No) AND (DATEDIFF(months, GETDATE(), s.Admission_Date) > 5);

Answer 1

Oracle has a months_between function:

AND MONTHS_BETWEEN(sysdate, s.Admission_Date) > 5

You could also use the add_months function:

AND s.Admission_Date < ADD_MONTHS(sysdate, -5)

which could still use an index on that column, if there is one.

And you can use intervals, but using month intervals for addition/subtraction can cause problems because of different month lengths.

---

Your logic doesn't look right though. For a start, you are comparing the reg_no values with both = and !=, which can't both be true at the same time. You're also looking at s.admission_date, not a date from enrolled_in.

You want to find students who haven't enrolled in a class in the last five months, so you could either find the most recent enrollment and filter those, something like (guessing you have a column called enrolled_date which you need to check):

SELECT s.Reg_No, s.First_Name, s.Last_Name, s.Admission_Date
FROM Student s
INNER JOIN Enrolled_In e ON s.Reg_No = e.Reg_No
GROUP BY s.Reg_No, s.First_Name, s.Last_Name, s.Admission_Date
HAVING MAX(e.Enrolled_Date) < ADD_MONTHS(sysdate, -5)

which will find students who have enrolled at some point, but not in the last five months. If you want to include students who have never enrolled you could use an outer join:

SELECT s.Reg_No, s.First_Name, s.Last_Name, s.Admission_Date
FROM Student s
LEFT OUTER JOIN Enrolled_In e ON s.Reg_No = e.Reg_No
AND e.Enrolled_Date >= ADD_MONTHS(sysdate, -5)
GROUP BY s.Reg_No, s.First_Name, s.Last_Name, s.Admission_Date
HAVING COUNT(e.Enrolled_Date) = 0

Or as you aren't reporting any data from the enrollment, you could use not exists with a subquery to find any recent course:

SELECT s.Reg_No, s.First_Name, s.Last_Name, s.Admission_Date
FROM Student s
WHERE NOT EXISTS (
    SELECT null
    FROM Enrolled_In e
    WHERE e.Reg_No = s.Reg_No
    AND e.Enrolled_Date >= ADD_MONTHS(sysdate, -5)
);

find the students which are in present in student table but not in enrolled table

You can use the not exists query for that too - just take out the date check:

SELECT s.Reg_No, s.First_Name, s.Last_Name, s.Admission_Date
FROM Student s
WHERE NOT EXISTS (
    SELECT null
    FROM Enrolled_In e
    WHERE e.Reg_No = s.Reg_No
);

Using an outer join and null check or count works too though, as you figured out for yourself.