Stored Procedure for three different date ranges

  • kran

    SSC Veteran

    Points: 278

    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'

  • Lynn Pettis

    SSC Guru

    Points: 442144

    How are the parameters for the procedure declared?  Are they declared as VARCHAR(somevalue) or as DATETIME?

     

  • kran

    SSC Veteran

    Points: 278

    @BeginDate DATETIME

    ,@EndDate DATETIME

  • ScottPletcher

    SSC Guru

    Points: 98186

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Lynn Pettis

    SSC Guru

    Points: 442144

    kran wrote:

    @BeginDate DATETIME ,@EndDate DATETIME

     

    Are these values being compared against other columns defined as DATETIME and do these values have a time component?  If so, you are doing your comparisons wrong for date ranges.

     

  • kran

    SSC Veteran

    Points: 278

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442144

    kran wrote:

    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?

     

  • kran

    SSC Veteran

    Points: 278

     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
  • kran

    SSC Veteran

    Points: 278

    I have to declare the variable only once then it will work.

  • kran

    SSC Veteran

    Points: 278

    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