Re: Selecting Dates

This should get you going


Get the first day of the previous month

  1* select to_date('01-'||to_char(add_months(sysdate,-1),'mon-YY'))  from
dual
SQL> /
01-FEB-03

Get the last day of the previous month

  1* select last_day(add_months(sysdate,-1))  from dual
SQL> /
28-FEB-03

 Good Luck

Regards
Chris Farmer



                                                                                                             
                      priede01@sprintsp                                                                      
                      ectrum.com               To:       "Oracle" <[email protected]>                      
                                               cc:                                                           
                      21/03/2003 14:59         Subject:  [oracle] Selecting Dates                            
                      Please respond to                                                                      
                      "Oracle"                                                                               
                                                                                                             
                                                                                                             




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
B.TRANS_TYPE_ID in(100,600,200) AND
B.SEQ_NO=A.SEQ_NO and
C.PRODUCT_TYP_ID in(4,5,2) and
C.product_id=A.product_id and
A.DIRECT_FULFILL='n'

Here is code that only pulls the prior month but only one day.

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 MONTHS_BETWEEN(sysdate, b.trans_dt)=1 and
B.TRANS_TYPE_ID in(100,600,200) AND
B.SEQ_NO=A.SEQ_NO and
C.PRODUCT_TYP_ID in(4,5,2) and
C.product_id=A.product_id and
A.DIRECT_FULFILL='n'

Here is code that works in VB.Net
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('" & Month(Now()) - 1 /01/2003','MM/DD/YYYY')
AND TO_DATE('" & Month(Now()) - 0 & "/01/2003','MM/DD/YYYY') and
B.TRANS_TYPE_ID in(100,600,200) AND
B.SEQ_NO=A.SEQ_NO and
C.PRODUCT_TYP_ID in(4,5,2) and
C.product_id=A.product_id and
A.DIRECT_FULFILL='n'


Thanks for yor help!
---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to %%email.unsub%%.






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