Between operator

[orajit]

 
SELECT empno,sal,job 
          FROM emp 
          WHERE deptno IN (SELECT deptno FROM dept) AND
           to_char(joindate ,'ddmmyyyy') BETWEEN 
           (START_DATE)   AND (END_DATE);
 

The above query will give me result set of employees who has joindate between startdate and enddate .
Now I wanted to Retrieve those employees whose joindate is not between startdate and enddate .
Could you Please tell me how to achieve the by using this query .

Thanks in advance .

[amitpatel66]

 
SELECT empno,sal,job 
          FROM emp 
          WHERE deptno IN (SELECT deptno FROM dept) AND
           to_char(joindate ,'ddmmyyyy') BETWEEN 
           (START_DATE)   AND (END_DATE);
 

The above query will give me result set of employees who has joindate between startdate and enddate .
Now I wanted to Retrieve those employees whose joindate is not between startdate and enddate .
Could you Please tell me how to achieve the by using this query .

Thanks in advance .

Try this:

 
SELECT empno,sal,job 
          FROM emp 
          WHERE deptno IN (SELECT deptno FROM dept) AND
           (to_char(joindate ,'ddmmyyyy') < (START_DATE)   OR to_char(joindate ,'ddmmyyyy') > (END_DATE));
 
 

[vikas000000a]

 
SELECT empno,sal,job 
          FROM emp 
          WHERE deptno IN (SELECT deptno FROM dept) AND
           to_char(joindate ,'ddmmyyyy') BETWEEN 
           (START_DATE)   AND (END_DATE);
 

The above query will give me result set of employees who has joindate between startdate and enddate .
Now I wanted to Retrieve those employees whose joindate is not between startdate and enddate .
Could you Please tell me how to achieve the by using this query .

Thanks in advance .

Simply use NOT BETWEEN instead of BETWEEN

That's it