Post reply

Stored Procedure for three different date ranges

  • April 12, 2019 at 3:14 pm

    #3160214

    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

    #3160330

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

     

  • April 12, 2019 at 3:33 pm

    #3160814

    @BeginDate DATETIME

    ,@EndDate DATETIME

  • April 12, 2019 at 3:33 pm

    #3160815

    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) A socialist is someone who will give you the shirt off *someone else's* back.

  • April 12, 2019 at 3:35 pm

    #3160904

    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.

     

  • April 12, 2019 at 3:44 pm

    #3161149

    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

    #3161377

    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?

     

  • April 12, 2019 at 3:50 pm

    #3161539 

     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

    #3162096

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

  • April 12, 2019 at 8:53 pm

    #3174933

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply