user123 user123 - 2 months ago 8
SQL Question

How to fetch the records from a table where joining date of an employee is less than 15th of every month?

This is the schema of my table EMP :

CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);


I have to list the employees those who joined in company before 15th of the month , so here month can be any month and we have to list name of all employees. I am unable to extract the month from hiredate and year from hiredate along with where clause as moth can be any month and year ranges from 1980 to 1990 so I will also have to check whether the year lies in the range of 1980-1990.

Answer

Use EXTRACT on the HIREDATE and check if it is less than 15:

SELECT * FROM EMP WHERE EXTRACT(day FROM HIREDATE) < 15

More about EXTRACT function: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm