How to display date and time from datetime columns when joining tables

Other SQL Server Topics
1

I have a registration_date column which includes both the date and the time of when staff registered which is located in the staff table and an enroll_date column which also includes the date and time but is stored in the enrollment table. I can join these using s.employeed_id = e.employee_id. The problem is Iwant to be able to retrieve the date and the time from the registered column as well as the date and time of staff who ENROLLED on 02-FEB-18 how can I do this? I have attempted but I don't get any results.

SELECT S.EMPLOYEE_ID, S.LAST_NAME, TO_CHAR (REGISTRATION_DATE, E.ENROLL_DATE, 'DD MON YY HH:MI:SS'), E.SECTION_ID
FROM STAFF S, ENROLLMENT E
where TO_CHAR (E.ENROLL_DATE, 'DD MON YY')= '30-JAN-07'
AND S.EMPLOYEE_ID = E.EMPLOYEE_ID;

2

As this forum is dedicated to Microsoft SQL Server, you might not get the best help here.

But try this:

SELECT S.EMPLOYEE_ID
      ,S.LAST_NAME
      ,TO_CHAR(S.REGISTRATION_DATE,'DD MON YY HH:MI:SS') REG_DATE
      ,TO_CHAR(E.ENROLL_DATE,'DD MON YY HH:MI:SS') ENR_DATE
      ,E.SECTION_ID
  FROM STAFF S
       INNER JOIN ENROLLMENT E
               ON E.EMPLOYEE_ID=S.EMPLOYEE_ID
              AND E.ENROLL_DATE>=TO_DATE('30-JAN-07','DD MON YY')
              AND E.ENROLL_DATE<TO_DATE('30-JAN-07','DD MON YY')+1
;
3

Ah thank you! How come I can't just put WHERE_TO_CHAR (E.ENROLL_DATE, 'DD-MON-YY' = '30-JAN-07';?

4

If you do it like you did, the sql engine would have to read all rows, apply the TO_CHAR function and then filter the rows containing your search date. An index wouldn't increase performance, but "my" method would be drastically faster when dealing with many rows..

So it's just for performance :slight_smile: