April 12, 2019 at 3:14 pm
Hi Guys,
I have to create a montly SQL agent job for Stored Procedure where it can pick dates automatically. I have a stored procedure with two parameters @begindate and @enddate. As this is April, I want this Stored procedure to run for 3 months. Now I am hard coding this, but I want this to change automatically. If it is may, the Procedure should run for Feb, March, and April. Can I create like below
Exec rpt_MonthlyApprovalsProductivity 'Dateadd(month,datediff(month,0,getdate())-3,0)', 'EOMONTH(Dateadd(month,datediff(month,0,getdate())-3,0))'
Exec rpt_MonthlyApprovalsProductivity 'Dateadd(month,datediff(month,0,getdate())-2,0)', 'EOMONTH(Dateadd(month,datediff(month,0,getdate())-2,0))'
Exec rpt_MonthlyApprovalsProductivity 'Dateadd(month,datediff(month,0,getdate())-1,0)', 'EOMONTH(Dateadd(month,datediff(month,0,getdate())-1,0))'
Both parameters @BeginDate DATETIME
,@EndDate DATETIME
But this is giving me the error below :
Msg 8114, Level 16, State 5, Procedure rpt_MonthlyApprovalsProductivity, Line 557
Error converting data type varchar to datetime.
Msg 8114, Level 16, State 5, Procedure rpt_MonthlyApprovalsProductivity, Line 557
Error converting data type varchar to datetime.
Msg 8114, Level 16, State 5, Procedure rpt_MonthlyApprovalsProductivity, Line 557
Error converting data type varchar to datetime.
Exec usp_Storedprocedure '1/1/2019', '1/31/2019'
Exec usp_Storedprocedure '2/1/2019', '2/28/2019'
Exec usp_Storedprocedure '3/1/2019', '3/31/2019'
April 12, 2019 at 3:20 pm
How are the parameters for the procedure declared? Are they declared as VARCHAR(somevalue) or as DATETIME?
April 12, 2019 at 3:33 pm
@BeginDate DATETIME
,@EndDate DATETIME
April 12, 2019 at 3:33 pm
You can't pass expressions as parameter values. You need to resolve the expression yourself and pass only a single (scalar) value. For example:
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0))
EXEC dbo.rpt_MonthlyApprovalsProductivity @start_date, @end_date
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))
EXEC dbo.rpt_MonthlyApprovalsProductivity @start_date, @end_date
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
EXEC dbo.rpt_MonthlyApprovalsProductivity @start_date, @end_date
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 12, 2019 at 3:44 pm
It gives me this error now If I declare and set values like above
Msg 134, Level 15, State 1, Line 582
The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 134, Level 15, State 1, Line 590
The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure.
April 12, 2019 at 3:48 pm
It gives me this error now If I declare and set values like above Msg 134, Level 15, State 1, Line 582 The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure. Msg 134, Level 15, State 1, Line 590 The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure.
Could you post the code?
April 12, 2019 at 3:50 pm
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-3,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-3,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-2,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-2,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-1,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-1,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
April 12, 2019 at 4:04 pm
I have to declare the variable only once then it will work.
April 12, 2019 at 8:53 pm
Declared variables only once, it worked. Thanks
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-3,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-3,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
SET @begindate =Dateadd(month,datediff(month,0,getdate())-2,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-2,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
SET @begindate =Dateadd(month,datediff(month,0,getdate())-1,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-1,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy