Question using BETWEEN/AND cond with NEXT_DAY/DAY math

From: Eugene Choy <echoy_at_alfred.carleton.ca>
Date: 20 Dec 92 20:13:43 GMT
Message-ID: <echoy.724882423_at_cunews>

i am some trouble understanding how the BETWEEN/AND condition works in conjunction with the NEXT_DAY function and DAYs arithmatic.

The following statement displays 'ok' >

SELECT 'ok' FROM DUAL WHERE 4 BETWEEN 4 AND 6;
SELECT 'ok' FROM DUAL WHERE 5 BETWEEN 4 AND 6;
SELECT 'ok' FROM DUAL WHERE 6 BETWEEN 4 AND 6;

which i assume that for the condition

   WHERE a BETWEEN b AND c
TRUE will be returned if

   b >= a >= c
This is fine since it agrees with the oracle:sql maunual

Now, consider this statement

   (assume NEXT_DAY(sysdate,'sunday') returns 3-jan-93):

---

SELECT 'ok' FROM DUAL
WHERE

   to_date('1-jan-93','dd-mm-yy') BETWEEN
   NEXT_DAY(sysdate,'sunday')-2 AND
   NEXT_DAY(sysdate,'sunday');


----------------------------

this does not return 'ok'!!!!!
why is this the case? since,     
   NEXT_DAY(sysdate,'sunday')-2                 means 01-jan-93
   NEXT_DAY(sysdate,'sunday')                   means 03-jan-93

pls email back or post , any suggestions will be appreciated

#----------------------------------------------------------------------------

from Eugene Choy
echoy_at_alfred.ccs.carleton.ca
Ottawa (the Nation's capital), Canada. North America. 'Software stands between the user and the machine'

#----------------------------------------------------------------------------

Received on Sun Dec 20 1992 - 21:13:43 CET