Re: Selecting Dates

Thanks this works wonderfully!

Thank you,
Doug Riede
Product Planning & Strategy
Subscriber Equipment
913-762-7315
Sprint PCS



-----Original Message-----
From: [email protected] [mailto:[email protected]]
Sent: Friday, March 21, 2003 8:52 AM
To: Oracle
Subject: [oracle] 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.unsub%%.

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