April 24, 2009 at 8:39 am
So perhaps I'm just having a brain-fart this morning, but I could use another set of eyes...
I'm in the process of automating a current manual task which involves running a stored procedure then a DTS task and a few other steps...
The stored procedure needs to be run with last week's dates to pull the appropriate data. It accepts 2 parameters @startDate Datetime and @EndDate Datetime.
I am able to run the code from SSMS like this.
DECLARE @StartDate DATETIME, @EndDate DATETIME
SELECT @StartDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0),
@EndDate = DateAdd(ms,-3, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))
EXECUTE [myProcedure] @StartDate, @EndDate
I can also execute it like this, which is basically what the user was doing via the manual process
EXECUTE [myProcedure]
@StartDate = '2009-04-13 00:00:00.000',
@EndDate = '2009-04-19 23:59:59.997'
Anyone know why I keep seeing an error when I try to execute it like this?
EXECUTE [myProcedure]
@StartDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0),
@EndDate = DateAdd(ms,-3, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))
I keep getting the below as an error.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'wk'.
As stated above I can execute this other ways, but I'm just wondering why? Any thoughts?
Thanks in advance,
-Luke.
April 24, 2009 at 8:49 am
It doesn't like calculating values within the execute command. Do it like your first example.
As an aside, I'd change the way you're doing the end date. Make it the exact end date, and make the proc use less than on that instead of between. It's a better practice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2009 at 9:09 am
GSquared (4/24/2009)
It doesn't like calculating values within the execute command. Do it like your first example.
Thanks Gus, not enough coffee this morning, just couldn't figure why it wouldn't work.
As an aside, I'd change the way you're doing the end date. Make it the exact end date, and make the proc use less than on that instead of between. It's a better practice.
Yeah I know about the < vs between bit, I'm fixing someone else's code and wanted to recreate the original process to make sure I had the data and logic correct before I started making wholesale changes to improve performance, structure and other such things.
Thanks again,
-Luke.
April 24, 2009 at 12:27 pm
Definitely know how that goes. Change one thing at a time, so if it breaks, you know exactly what to undo.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2009 at 12:36 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply