Re: Need Help with PL/SQL

In <NEWTNews.843740213.31650.atyagi_at_sgeier.wsp>, atyagi_at_mc.xerox.com writes:
>
>
>Hello Everybody,
>
> I have a table containing a field ship_date. I wish to
>select the shipment dates excluding weekends. I only want
>data for weekdays. Here is the query I have written.
>
> SELECT EO_SHIPPED.SHIP_DATE, EO_SHIPPED.ORDERNUM
>FROM EO_SHIPPED
>WHERE ((EO_SHIPPED.SHIP_DATE BETWEEN '14-SEP-96' AND '15-SEP-96')
> AND
> EO_SHIPPED.SHIP_DATE NOT IN
> (SELECT EO_SHIPPED.SHIP_DATE
> FROM EO_SHIPPED
> WHERE TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY')='SATURDAY'
> OR TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY')= 'SUNDAY'
> ));
>
>I tried running subquery seprately but it seems that it is
>not liking my comparision with Sat. & Sun and doesn't return
>me anything.
>
>I will appreciate any help.
>
>Thanks all.
>
>You can mail me at atyagi_at_mc.xerox.com
>or
>post your comments in newsgroup.
>
>Amit Tyagi
>

I just tried the same thing and I also cannot get a valid comparison using 'DAY' as the format. However, using 'DY' or 'D' works as expected. For example:

   WHERE TO_CHAR(EO_SHIPPED.SHIP_DATE,'DY') = 'SAT'          OR TO_CHAR(EO_SHIPPED.SHIP_DATE,'DY') = 'SUN'

   WHERE TO_CHAR(EO_SHIPPED.SHIP_DATE,'D') = 7       -- Sat = 7
         OR TO_CHAR(EO_SHIPPED.SHIP_DATE,'D') = 1    -- Sun = 1

M.Landa Received on Fri Sep 27 1996 - 00:00:00 CEST