DTS Newbie Question

[Schnappa]

Hi All

Hoping someone can help. Please find below a DTS package I have created, and am having difficulty running (seperated into two parts).

______________________
CREATE TABLE [TESTNEWTRAN] (
[DPTranNumber] decimal (12,0) NOT NULL,
[AccountID] char (12) NOT NULL,
[TranCode] smallint NOT NULL,
[TranAmount] decimal (21,6) NOT NULL,
[TranDate] smalldatetime NOT NULL,
[TranNarration] varchar (80) NULL,
[Operatorname] varchar (40) NULL,
[LongTranNarration] varchar (255) NULL,
[TranBranch] varchar (15) NULL,
[TranOriginDesc] varchar (40) NULL,
[TranChannelDesc] char (20) NULL
)
__________________________________________
Declare @StartDate DateTime
Declare @EndDate DateTime

Set @EndDate = DateAdd(Month, DateDiff(month, 0, GetDate()), 0)
Set @StartDate = DateAdd(Month, -1, @EndDate)

SELECT

dp_history.ptid AS DPTranNumber,
dp_history.acct_no AS AccountID,
dp_history.tran_code AS TranCode,
dp_history.amt AS TranAmount,
dp_history.effective_dt AS TranDate,
dp_history.description AS TranNarration,
ad_gb_rsm.name AS Operatorname,
dp_history.long_desc AS LongTranNarration,
ad_gb_branch.short_name AS TranBranch,
pc_origin.description AS TranOriginDesc,
ad_gb_channel.short_desc AS TranChannelDesc


FROM

(((tstUCU.dbo.dp_history dp_history LEFT OUTER JOIN tstUCU.dbo.ad_gb_rsm ad_gb_rsm ON dp_history.empl_id=ad_gb_rsm.employee_id) LEFT OUTER JOIN tstUCU.dbo.pc_origin pc_origin ON dp_history.origin_id=pc_origin.origin_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_channel ad_gb_channel ON dp_history.channel_id=ad_gb_channel.channel_id) LEFT OUTER JOIN tstUCU.dbo.ad_gb_branch ad_gb_branch ON ad_gb_rsm.branch_no=ad_gb_branch.branch_no


WHERE

dp_history.effective_d >= @StartDate
And dp_history.effective_d < @EndDate

______________________________________________________

When I run this I get an error message "...Function DATEDIFF invoked with wrong number or type of argument".

Cheers

GV

 

[rac2]

Strange as that expression does not give an error in Query Analyzer. Alternatives

DateAdd(Month, DateDiff(month, CAST(0 AS DATETIME), GetDate()), 0)

DateAdd(Month, DateDiff(month, '1900-01-01', GetDate()), 0)