CODEWITHSUNDEEP
oracle-databasedate
intruder
intruder

Reputation: 417

Query to display rows from a table related to certain number of days in Oracle

I'm a newbie to DB and I've a question regarding displaying contents from a table based on date.

How can I search for all the entities from EMPLOYEE table which matches certain value in EmpPayScaleID and +/- 3 days of EmpPayDate

For example,
I need to print all the values form EMPLOYEE table where EmpPayScaleId is 1001 and EmpPayDate should be +/- 3 days from 01-AUG-17 (should show records from 29-JUL-17 to 04-SEP-17)

I tried this: 

SELECT * FROM EMPLOYEE
              WHERE EmpPayScaleId = 1001
              AND EmpPayDate in ADD_MONTHS(01-Aug-17, -3, dd-mm-yy)  
              AND EmpPayDate in ADD_MONTHS(01-Aug-17, 3, dd-mm-yy);

Correction: 

SELECT * FROM EMPLOYEE
              WHERE EmpPayScaleId = 1001
              AND EmpPayDate in ADD_MONTHS(01-Aug-17, -3, 'DD-MON-YY')  
              AND EmpPayDate in ADD_MONTHS(01-Aug-17, 3, 'DD-MON-YY');

This shows nothing. Where was my mistake?
Is this a correct query?

Edit:
It shows invalid number of arguments when I use ADD_MONTHS(01-AUG-17, 3, 'DD-MON-YY')

Upvotes: 0

Views: 27

Answers (1)

wolfrevokcats
wolfrevokcats

Reputation: 2100

The date in your query has been specified incorrectly. The following where clause is enough:

...
WHERE EmpPayScaleId = 1001
          AND to_date('01-Aug-17','DD-MON-YY') between EmpPayDate-3 
                                                   and EmpPayDate+3;

Upvotes: 1

Related Questions