locked
Need to add in Stored procedure in Dateperiod Parameter RRS feed

  • Question

  • Hi,

    I am creating one store procedure like as below. I selecting in SSRS parameters Null with StartDate and EndDate  and DatePeriod parameter is Last 6Months data.  So pease help me out how to create the store procedure in SSMS.

    Create Proc SP_dateperiod

    @startTime DATETIME=null

    @EndDate DATETIME=null

    @Dateperiod datetime=null

    IF (@BeginDate IS NOT NULL AND @EndDate IS NOT NULL AND NOT(@BeginDate >= @EndDate))

                  BEGIN

                         DECLARE @PROD_TABLE TABLE

                         (

                               PT_PROD_ID INTEGER,

                               PRIMARY KEY (PT_PROD_ID)

                         )

                         DECLARE @AREA_TABLE TABLE

                         (

                               AT_AREA_ID INTEGER,

                               PRIMARY KEY (AT_AREA_ID)

                         )

    Thanks,

    Priyanka.

          

    Monday, July 6, 2020 11:35 AM

All replies

  • Hi Priyanka1592,

    Could you clarify more about what your demands and exception on SP? It hard to understand what you want according to post mentioned. It will better to introduce with some example picture. 

    And you also can post this question in T-SQL forums where you can get more effective help on T-SQL developing. 

    Best Regards

    Dawn


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 7, 2020 6:43 AM
  • Some example

    CREATE PROCEDURE GetObviousData
        @startdate DATETIME =NULL,
        @enddate DATETIME =NULL
    AS

    IF @startdate  IS NOT NULL AND @enddate IS NOT NULL

    BEGIN
        --SELECT statement
        SELECT t1.<Field_List2>, t2.<Field_List>
        FROM Emp AS t1 INNER JOIN EmpTypes AS t2 ON t1.EmpTypeID = t2.EmpTypeID
        WHERE  t1.HireDate BETWEEN @startdate AND @enddate
    END


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 7, 2020 7:00 AM
  • I cant able pass customised dates in this stored porcedure .

    When i am select @indate is 4(Customs dates) this procedure throghing error .

    So please helpme this proc.

    DECLARE @INDATE   INT   = 4  

    DECLARE @INSTART  NVARCHAR(10)='01/01/2019'      
    DECLARE @INEND   NVARCHAR(10)  ='12/31/2019'    


    DECLARE @STARTDATE  DATETIME      
    DECLARE @ENDDATE  DATETIME      

     IF @INDATE = 4      
     BEGIN      
       IF @INSTART = ''      
        BEGIN      
          SET @INSTART =DATEADD(YYYY,-1,GETDATE())    
        SET @INEND =GETDATE()    

         --SET @INSTART = '01' + CAST(DATEPART(YYYY,DATEADD(YYYY,-1,GETDATE())) AS NVARCHAR(4))      
         --SET @INEND = '01' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4))      
        END      
        IF @INEND = ''      
        BEGIN      
          SET @INEND=DATEADD(YYYY,1,@INSTART)    
        -- SET @INEND = '01' + CAST(CAST(RIGHT(@INSTART,4) AS INT) + 1 AS NVARCHAR(4))      
        END      
     END      

     IF @INDATE = 1      
      BEGIN      
      SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
      SET @STARTDATE = (SELECT DATEADD(YEAR, -1, @ENDDATE))      
      END      
     ELSE      
     IF @INDATE = 2      
       BEGIN      
       SET @ENDDATE = ( SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
        IF DATEPART(MM,GETDATE()) = 1      
        BEGIN      
         SET @STARTDATE = (SELECT DATEADD(YEAR, -1, @ENDDATE))      
        END      
        ELSE      
        BEGIN      
         SET @STARTDATE = (SELECT CAST('1/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
        END      
       END      
     ELSE      
     IF @INDATE = 3      
      BEGIN      
      SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' +  CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME)  )      
      SET @STARTDATE = (SELECT DATEADD(YEAR, -2, @ENDDATE))      
      END      

        ELSE      
      BEGIN      
       DECLARE @STARTMONTH INT = (SELECT DATEPART(MONTH,@INSTART))    
       --DECLARE @STARTMONTH INT = (SELECT CAST(LEFT(@INSTART,2) AS INT))      
      -- PRINT @STARTMONTH    
       --SELECT DATEPART(YEAR,@INSTART)    

       SET @STARTDATE = CAST(CAST(@STARTMONTH AS NVARCHAR(2)) + '/1/' + (SELECT CAST(DATEPART(YEAR,@INSTART) AS NVARCHAR(4))) AS DATETIME)    
       --SET @STARTDATE = CAST(CAST(@STARTMONTH AS NVARCHAR(2)) + '/1/' + RIGHT(@INSTART,4) AS DATETIME)      
      --PRINT  @STARTDATE    

       DECLARE @END_MONTH_YEARE VARCHAR(6)    

       SELECT @END_MONTH_YEARE= CAST(DATEPART(MONTH,@INEND) AS NVARCHAR(2)) +' ' + CAST(DATEPART(YEAR,@INEND) AS NVARCHAR(4))    
      -- PRINT @END_MONTH_YEARE    

      -- PRINT LEN(@END_MONTH_YEARE)    

        IF LEN(@END_MONTH_YEARE) > 0      
         BEGIN      
          IF CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 = 13      
           BEGIN      
            SET @ENDDATE = (SELECT CAST('1/1/' + CAST((CAST(RIGHT(@END_MONTH_YEARE, 4) AS INT) + 1) AS NVARCHAR(4)) AS DATETIME))      
           END      
          ELSE      
           BEGIN      
            SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEARE, 4) AS DATETIME)      
           )      
           END      
         END      
        ELSE      
         BEGIN      
          SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEARE, 4) AS DATETIME))      
         END      
       END      

      IF @INDATE = 5     
      BEGIN      
      SET @STARTDATE = (SELECT CAST(CAST(CAST(LEFT('1',2) AS INT)  AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YEAR,GETDATE())-1 AS NVARCHAR(4)) AS DATETIME))    

            set @ENDDATE= (SELECT CAST(CAST(CAST(LEFT('1',2) AS INT)  AS NVARCHAR(2)) + '/1/' + CAST(datepart(YEAR,GETDATE()) AS NVARCHAR(4)) AS DATETIME))    


      --SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' +  CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME)  )      
      --SET @STARTDATE = (SELECT DATEADD(YEAR, -2, @ENDDATE))      
      END      

    SELECT @ENDDATE AS ENDDATE, @STARTDATE AS STARTDATE

    Friday, August 7, 2020 6:26 AM
  • I cant able pass customised dates in this stored porcedure .

    When i am select @indate is 4(Customs dates) this procedure throghing error .

    So please helpme this proc.

    DECLARE @INDATE   INT   = 4  

    DECLARE @INSTART  NVARCHAR(10)='01/01/2019'      
    DECLARE @INEND   NVARCHAR(10)  ='12/31/2019'    


    DECLARE @STARTDATE  DATETIME      
    DECLARE @ENDDATE  DATETIME      

     IF @INDATE = 4      
     BEGIN      
       IF @INSTART = ''      
        BEGIN      
          SET @INSTART =DATEADD(YYYY,-1,GETDATE())    
        SET @INEND =GETDATE()    

         --SET @INSTART = '01' + CAST(DATEPART(YYYY,DATEADD(YYYY,-1,GETDATE())) AS NVARCHAR(4))      
         --SET @INEND = '01' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4))      
        END      
        IF @INEND = ''      
        BEGIN      
          SET @INEND=DATEADD(YYYY,1,@INSTART)    
        -- SET @INEND = '01' + CAST(CAST(RIGHT(@INSTART,4) AS INT) + 1 AS NVARCHAR(4))      
        END      
     END      

     IF @INDATE = 1      
      BEGIN      
      SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
      SET @STARTDATE = (SELECT DATEADD(YEAR, -1, @ENDDATE))      
      END      
     ELSE      
     IF @INDATE = 2      
       BEGIN      
       SET @ENDDATE = ( SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
        IF DATEPART(MM,GETDATE()) = 1      
        BEGIN      
         SET @STARTDATE = (SELECT DATEADD(YEAR, -1, @ENDDATE))      
        END      
        ELSE      
        BEGIN      
         SET @STARTDATE = (SELECT CAST('1/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
        END      
       END      
     ELSE      
     IF @INDATE = 3      
      BEGIN      
      SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' +  CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME)  )      
      SET @STARTDATE = (SELECT DATEADD(YEAR, -2, @ENDDATE))      
      END      

        ELSE      
      BEGIN      
       DECLARE @STARTMONTH INT = (SELECT DATEPART(MONTH,@INSTART))    
       --DECLARE @STARTMONTH INT = (SELECT CAST(LEFT(@INSTART,2) AS INT))      
      -- PRINT @STARTMONTH    
       --SELECT DATEPART(YEAR,@INSTART)    

       SET @STARTDATE = CAST(CAST(@STARTMONTH AS NVARCHAR(2)) + '/1/' + (SELECT CAST(DATEPART(YEAR,@INSTART) AS NVARCHAR(4))) AS DATETIME)    
       --SET @STARTDATE = CAST(CAST(@STARTMONTH AS NVARCHAR(2)) + '/1/' + RIGHT(@INSTART,4) AS DATETIME)      
      --PRINT  @STARTDATE    

       DECLARE @END_MONTH_YEARE VARCHAR(6)    

       SELECT @END_MONTH_YEARE= CAST(DATEPART(MONTH,@INEND) AS NVARCHAR(2)) +' ' + CAST(DATEPART(YEAR,@INEND) AS NVARCHAR(4))    
      -- PRINT @END_MONTH_YEARE    

      -- PRINT LEN(@END_MONTH_YEARE)    

        IF LEN(@END_MONTH_YEARE) > 0      
         BEGIN      
          IF CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 = 13      
           BEGIN      
            SET @ENDDATE = (SELECT CAST('1/1/' + CAST((CAST(RIGHT(@END_MONTH_YEARE, 4) AS INT) + 1) AS NVARCHAR(4)) AS DATETIME))      
           END      
          ELSE      
           BEGIN      
            SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEARE, 4) AS DATETIME)      
           )      
           END      
         END      
        ELSE      
         BEGIN      
          SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEARE, 4) AS DATETIME))      
         END      
       END      

      IF @INDATE = 5     
      BEGIN      
      SET @STARTDATE = (SELECT CAST(CAST(CAST(LEFT('1',2) AS INT)  AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YEAR,GETDATE())-1 AS NVARCHAR(4)) AS DATETIME))    

            set @ENDDATE= (SELECT CAST(CAST(CAST(LEFT('1',2) AS INT)  AS NVARCHAR(2)) + '/1/' + CAST(datepart(YEAR,GETDATE()) AS NVARCHAR(4)) AS DATETIME))    


      --SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' +  CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME)  )      
      --SET @STARTDATE = (SELECT DATEADD(YEAR, -2, @ENDDATE))      
      END      

    SELECT @ENDDATE AS ENDDATE, @STARTDATE AS STARTDATE

    Friday, August 7, 2020 6:26 AM