Thanks this is working!
Thank you,
Doug Riede
Product Planning & Strategy
Subscriber Equipment
xxx-xxx-xxxx
Sprint PCS



-----Original Message-----
From: Brian Freeman [mailto:freeman@C...]
Sent: Tuesday, March 18, 2003 2:29 PM
To: sql language
Subject: [sql_language] RE: Selecting dates


GetDate(), datepart() are SQL Server functions.  It looks like for Oracle it
would be:
1.  return everything in last month (i.e. 2/1/03-2/28/03 23:59:59) using
months_Between

Select  distinct C.SKU, C.Short_nm
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,200,600) 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.qty>0

2. if truly want 2/1/03-3/1/03 then (use trunc to get 1/1 of current month,
subtract 1 day (to get last day of previous month) and use trunc to return
first of previous month

Select  distinct C.SKU, C.Short_nm
from 
RMS.TRANSACTION_ITEMS A, 
RMS.TRANSACTION_HDR B, 
RMS.PRODUCT_MASTER C 
WHERE B.TRANS_DT
between trunc(trunc(sysdate,'month')-1)
and trunc(sysdate,'month')
AND
B.TRANS_TYPE_ID in(100,200,600) 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.qty>0


Brian Freeman
(xxx) xxx-xxxx  ext. 415
Carnegie Technologies/Bluewave Computing 
www.carnegie.com and www.bluewave-computing.com

-----Original Message-----
From: Riede, Doug D [PCS] [mailto:priede01@s...]
Sent: Tuesday, March 18, 2003 3:05 PM
To: sql language
Subject: [sql_language] RE: Selecting dates


Thanks for your quick response.
This still does not work.
I don't believe it is the code. Your code looks great!
I am using DTS to transfer data from one server to another. Source is Oracle
and target is SQL server.
I am choosing use query to specify the data to transfer.
The error is ORA00936 Missing Expression on you second example and 
The first example gives me the error 00904 invalid column name
Any idea what is wrong?  

Thank you,
Doug Riede
Product Planning & Strategy
Subscriber Equipment
xxx-xxx-xxxx
Sprint PCS



-----Original Message-----
From: Brian Freeman [mailto:freeman@C...]
Sent: Tuesday, March 18, 2003 12:10 PM
To: sql language
Subject: [sql_language] RE: Selecting dates


Two options:
1.  return everything in last month (i.e. 2/1/03-2/28/03 23:59:59) using
datediff

Select  distinct C.SKU, C.Short_nm
from 
RMS.TRANSACTION_ITEMS A, 
RMS.TRANSACTION_HDR B, 
RMS.PRODUCT_MASTER C 
WHERE 
datediff(month,b.trans_dt,getdate())=1 and 
B.TRANS_TYPE_ID in(100,200,600) 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.qty>0

2. if truly want 2/1/03-3/1/03 then (use datepart to get current month,
concatenate date string and convert to date for between)

Select  distinct C.SKU, C.Short_nm
from 
RMS.TRANSACTION_ITEMS A, 
RMS.TRANSACTION_HDR B, 
RMS.PRODUCT_MASTER C 
WHERE B.TRANS_DT
between convert(datetime,convert(varchar(2),datepart(month,getdate())-1) +
'/1/' + convert(varchar(4),datepart(year,getdate()),101))
and  convert(datetime,convert(varchar(2),datepart(month,getdate())) + '/1/'
+ convert(varchar(4),datepart(year,getdate()),101))
AND
B.TRANS_TYPE_ID in(100,200,600) 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.qty>0


Brian Freeman
(xxx) xxx-xxxx  ext. 415
Carnegie Technologies/Bluewave Computing 
www.carnegie.com and www.bluewave-computing.com

-----Original Message-----
From: priede01@s... [mailto:priede01@s...]
Sent: Tuesday, March 18, 2003 1:02 PM
To: sql language
Subject: [sql_language] Selecting dates


I am trying to select the dates that start at the first of the month and 
end at the first of the next month. I would be selecting 02/01/2003 to 
03/01/2003 if I ran this any time between 03/01/2003 and 03/31/2003. I 
need the month to change based on what month you are running this code. 
Below is the SQL that I am using in VB.net. I am moving this code to DTS 
and need help on the date range.

SQL1 = "Select  distinct C.SKU, C.Short_nm " _
            & "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()) 
& "/01/2003','MM/DD/YYYY') and " _
            & "B.TRANS_TYPE_ID in(100,200,600) 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.qty>0 "


This code works in DTS but I need the Month to change based on the current 
month.
Select  distinct C.SKU, C.Short_nm
from 
RMS.TRANSACTION_ITEMS A, 
RMS.TRANSACTION_HDR B, 
RMS.PRODUCT_MASTER C 
WHERE 
B.TRANS_DT between '02/01/2003' AND '03/01/2003' and 
B.TRANS_TYPE_ID in(100,200,600) 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.qty>0


Thanks for your help!