getting between dates in oracle

Question

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?

From the docs: If expr3 < expr2, then the interval is empty. — Alex Poole
– Alex Poole, Commented Oct 25, 2015 at 14:09
Also remember that SYSDATE has a time component, so if you are running the query at 2:00pm, SYSDATE - 20 will also be from 2:00pm. Often when checking days you will want to use: WHERE attendance_date BETWEEN TRUNC(SYSDATE - 20) AND TRUNC(SYSDATE) — Glenn
– Glenn, Commented Oct 25, 2015 at 14:20

score 6 · Accepted Answer · 2015-10-25 14:08:41Z

6

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.

edited Oct 25, 2015 at 14:08

answered Oct 25, 2015 at 14:06

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Lalit Kumar B Over a year ago

Unfortunately Oracle does not support symmetric .

user330315 Over a year ago

@LalitKumarB: that's what I wrote.

Lalit Kumar B Over a year ago

@a_horse_with_no_name Yes, I see. +1

Lukasz Szozda · Accepted Answer · 2015-10-25 14:04:31Z

3

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

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

answered Oct 25, 2015 at 14:04

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

getting between dates in oracle

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related