Re: Another annoying SQL date question...

In article <4qjsbp$e2o_at_knot.queensu.ca> 3srf_at_qlink.queensu.ca (Frampton Steve R) writes:
> Tim Perrault (timp_at_asymetrix.com) wrote:
>
> : SELECT 'Yes'
> : FROM DUAL
> : WHERE '6' BETWEEN TO_CHAR(TO_DATE('061096','MMDDYY'),'D') AND
> : TO_CHAR(TO_DATE('060196','MMDDYY'),'D');
>
> Thanks Tim...although...seeing the solution *does* make me feel really
> stupid... :-}

I don't know who is stupid, but solution above don't work.

Try for example.

 SELECT 'Yes'
   FROM DUAL
  WHERE '6' BETWEEN TO_CHAR(TO_DATE('052596','MMDDYY'),'D') AND                     TO_CHAR(TO_DATE('060196','MMDDYY'),'D'); And besides, it depends on your localization. For example here in Finland TO_CHAR(TO_DATE('060196','MMDDYY'),'D') returns '6' because we count days: 1=Monday,,7=Sunday.

So, the right solution might be something like this:

 /*
  Here we are checking if next Friday or Monday from given start-date   is before or same as given end-date. NEXT_DAY returns next weeks   date if parameter is already friday (or monday), so we have to   decrement start-date by one.
  1.1.1996 = Monday and 5.1.1996 = Friday, so we dont have to care   about used language (except in this comment ;-)  */

 DEFINE bday=27051996
 DEFINE eday=30051996

 SELECT 'Yes'
   FROM DUAL
  WHERE (NEXT_DAY(TO_DATE('&bday','DDMMYYYY')-1

                  ,to_char(to_date('01011996','DDMMYYYY'),'DAY'))
 	 <= TO_DATE('&eday','DDMMYYYY'))
     OR (NEXT_DAY(TO_DATE('&bday','DDMMYYYY')-1
                  ,to_char(to_date('05011996','DDMMYYYY'),'DAY'))
 	 <= TO_DATE('&eday','DDMMYYYY'));


						olli R