Re: Question using BETWEEN/AND cond with NEXT_DAY/DAY math

In article <echoy.724882423_at_cunews>, echoy_at_alfred.carleton.ca (Eugene Choy) writes:
>

 stuff deleted
>
> 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
>

I know you said 'assume NEXT_DAY(sysdate,'sunday') returns 3-jan-93' but I believe if you run the query on between the 27th Dec 1992 and 2nd Jan 1993 you
should get the right answer.

Why not try

SELECT 'ok' FROM DUAL
WHERE
   to_date('26-DEC-92','dd-mon-yy') BETWEEN    NEXT_DAY(sysdate,'sunday')-2 AND
   NEXT_DAY(sysdate,'sunday');
now.

It gives the correct result.

-- 
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  __       /_ __ /       |Colonial Mutual Invest Mgmt Aust (ACN 004021809)
 /(_)/ ((_/ \_/(///(/_)/_(   |EMAIL:  TJambu_at_cmutual.com.au
 \_______/                   |PHONE:  +61-3-6418448       FAX:  +61-3-6076198