Re: Selecting Dates

[email protected] posted:

>I am looking for records in the prior month. If you are in March and run 
>this SQL then the records return would be all Feb records.
>Here is code that works!
>
>Select  B.empl_ba_id,a.qty as Qty, B.Trans_DT, A.Unit_Price, 
>B.REF_TRANS_NO, B.TRANS_TYPE_ID,B.Loc_ba_id, A.PRODUCT_ID  
>from RMS.TRANSACTION_ITEMS A, RMS.TRANSACTION_HDR B, RMS.PRODUCT_MASTER C 
>WHERE 
>B.TRANS_DT between TO_DATE('02/01/2003','MM/DD/YYYY') AND TO_DATE
>('02/28/2003','MM/DD/YYYY') and ...

I think a slightly more efficient way of achieving the date restriction
would be as follows:

to_char(b.trans_dt,'mm/yyyy') = to_char(add_months(sysdate,-1),'mm/yyyy')

You still have two function calls in that part of the query (with the right
part of the statement probably being optimized to a constant), but only
one comparison operation. Also, there is no ambiguity of 'between' being
inclusive on the endranges or not; nor any potential conversion problems
with the time of day coming into play (I know that this can happen in Oracle).

Roy Zimmer----->OIT----->Library Stuff & Other things----->
Western Michigan University----->Kalamazoo, Michigan USA
localsystem=P3  ICBM=(%Fatal:GPS error 51)  RF=KB8UBA  AF="Hey you!" QRM!
[email protected] you go, there you are!

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].