CODEWITHSUNDEEP
sqloracle-databasedatetime
user2093576
user2093576

Reputation: 3092

getting between dates in oracle

I am able to fetch values for :

SELECT * FROM table WHERE ATTENDANCE_DATE < SYSDATE;

but no records for 

SELECT * FROM table WHERE ATTENDANCE_DATE between SYSDATE and SYSDATE -20;

what is wrong in this?

Upvotes: 2

Views: 57

Answers (2)

user330315
user330315

Reputation:

If you re-write the second where clause to what it means, it's obvious.

The expression between SYSDATE and SYSDATE -20 is equivalent to:

where ATTENDANCE_DATE >= sysdate and ATTENDANCE_DATE <= sysdate - 20;

What you mean is: 

where ATTENDANCE_DATE between sysdate - 20 and sysdate;

Theoretically the SQL standard defines the symmetric option for between which allows any "order" of values, so that the following would do what you want:

between symmetric SYSDATE and SYSDATE - 20

But unfortunately Oracle does not support this.

Upvotes: 6

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

Your where condition is always false. You need to change the order.

SELECT * 
FROM table 
WHERE ATTENDANCE_DATE BETWEEN SYSDATE - 20 AND SYSDATE;

Upvotes: 3

Related Questions