CODEWITHSUNDEEP
databaseoracle-databasestored-proceduresoracle11g
user968441
user968441

Reputation: 1471

How to check if specified date contains in records or not

I want something strange here. I've table names as EMP_INFO which contains few details of an employee (i.e. Name,Designation, JOIN_FROM, JOIN_TO). I am trying to figure out term for each employee on yearly basis. I've below types of data

EMP_ID   EMP_DESIG     JOIN_FROM    JOIN_TO                Query Result
  1      Supervisor    01-05-11     30-04-13               Should Display
  2      Supervisor    15-06-10     31-12-12               Should Display
  3      Jobar         01-01-12     31-12-13               Should Display
  4      SR Superior   01-12-11     31-12-15               Should Display
  5      Supervisor    01-05-11     31-12-13               Should Display
  6      Supervisor    01-05-11     31-12-13               Should Display
  7      Supervisor    01-05-11     31-12-13               Should Display
  8      Supervisor    01-02-12     15-06-13               Should Display
  9      SR Superior   16-03-10     18-11-11               Should Display
  10     SR Superior   16-06-05     18-11-11               Should Display
  11     Jobar         30-11-11     31-12-13               Should Display
  12     Superior      02-02-05     31-12-20               Should Display
  13     Jobar         30-11-11     31-12-13               Should Display
  14     Jobar         30-11-09     31-12-10               Should Not Display

Basically what i need is I have date range in my report and let's say From: "01-Jun-11" To "31-Dec-13". From above record set report should retrieve all records as all records contains this both dates.

I have tried by using BETWEEN syntax but i believe it will not work.

If anyone can help me in this than it would be appreciated.

Thanks in Advance.. And one more thing if this details is not enough to understand than let me know i will add more in details.

Modified Query which I tried

SELECT EI.*     
FROM EMP_INFO EI,     
     (SELECT 
     TO_DATE('01-JUN-2011','DD-MON-YYYY') A,
     TO_DATE('31-DEC-2013','DD-MON-YYYY') B FROM DUAL) X   
WHERE   
(EI.JOIN_FROM IS NOT NULL AND EI.JOIN_TO IS NOT NULL)    
AND   (
           X.A BETWEEN EI.JOIN_FROM AND EI.JOIN_TO   
       AND X.B BETWEEN EI.JOIN_FROM AND EI.JOIN_TO   
       OR (EI.JOIN_FROM >= X.B   AND EI.JOIN_TO <=X.A)    )

Modified Added column (Query Result) on above table which contains result for each record.

Upvotes: 0

Views: 93

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

So you simply want all records where the join time is in the given time range? That would be:

SELECT *     
FROM EMP_INFO
WHERE JOIN_FROM BETWEEN 
  TO_DATE('01-JUN-2011','DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') AND 
  TO_DATE('31-DEC-2013','DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
AND JOIN_TO BETWEEN 
  TO_DATE('01-JUN-2011','DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') AND 
  TO_DATE('31-DEC-2013','DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN');

EDIT: Sorry, I got it now. You are looking for all time ranges that overlap with the given range. That would be: ranges that start before and end within, ranges that start before and end after, ranges that start within and end within and ranges that start within and end after. Another way to express this is: Either the given time range start is within the other time range or the other time range start is within the given time range. Here is the according statement:

SELECT *     
FROM EMP_INFO
WHERE JOIN_FROM BETWEEN 
  TO_DATE('01-JUN-2011','DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') AND 
  TO_DATE('31-DEC-2013','DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
OR TO_DATE('01-JUN-2011','DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
  BETWEEN JOIN_FROM AND JOIN_TO;

And here is the SQL fiddle: http://sqlfiddle.com/#!4/b58b3/3

Upvotes: 1

AbhinavRanjan
AbhinavRanjan

Reputation: 1646

Convert to same format and compare. There may be a time component in the dates stored in database. Previous answer was wrong.

Upvotes: 0

Related Questions