Skip to content

Answers to date problems

  1. SELECT TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') FROM employees;

  2. SELECT first_name, last_name, EXTRACT(YEAR FROM hire_date) FROM employees;

  3. SELECT EXTRACT(YEAR FROM hire_date) AS "Hire year", count(*) FROM employees GROUP BY hire_date;

  4. SELECT first_name, last_name, LAST_DAY(hire_date) AS "First payment" FROM employees;

  5. SELECT e.first_name, e.last_name, j.job_title, h.start_date, h.end_date FROM employees e JOIN job_history h ON e.employee_id = h.employee_id JOIN jobs j ON j.job_id = h.job_id ORDER BY e.employee_id, h.start_date;

  6. SELECT e.first_name, e.last_name, j.job_title, h.start_date, h.end_date, ROUND(MONTHS_BETWEEN(h.end_date, h.start_date), 0) AS Months FROM employees e JOIN job_history h ON e.employee_id = h.employee_id JOIN jobs j ON j.job_id = h.job_id ORDER BY e.employee_id, h.start_date;

  7. SELECT first_name, last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date),0) AS Months FROM employees ORDER BY Months DESC;

  8. SELECT first_name, last_name, hire_date FROM employees WHERE hire_date > TO_DATE('01-JAN-2000','DD-MON-YYYY');

  9. SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN TO_DATE('01-JAN-1990','DD-MON-YYYY') AND TO_DATE('31-DEC-1999','DD-MON-YYYY');

  10. SELECT e.first_name, e.last_name, j.job_title FROM employees e JOIN job_history h ON (e.employee_id = h.employee_id) JOIN jobs j ON (j.job_id = h.job_id) WHERE h.start_date = (SELECT MIN(j2.start_date) FROM job_history j2 WHERE j2.employee_id = e.employee_id);