Got me confused

[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

 

[Catadmin]

Your problem is this line:

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

You can't use 0 as a date to subtract GetDate() from. Both parts after the "month" MUST be valid dates.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AngelB]

Hi there

It is possible to use zero in datediff in this way. The code displayed above is a nice workaround when you want to display a datetime with the time portion as 00:00:00. (In this case the first of the month will be returned). At first glance I can't see a reason why this error should be displayed. I'll post back if I get more time to look at it.


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[Catadmin]

AngelB,

Whoops. You're correct. When I run only the Declare...Set statements as Schnappa has written them above, they work fine.

Schnappa, are you missing pieces of code from the above Select statement? I see a "FROM (((" with a whole bunch of joins, but don't see the corresponding ")))" on the code, which makes me think you left something out.

Also, do you use DateDiff in any other place in your package?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"