locked
I cant able pass customized dates RRS feed

  • Question

  • Hi All,

    I cant able pass customized dates. when i excute Indate=4  its passing null values so please help me this Proc.


    declare

     @INDATE INT= 4,

    @INSTART DATE='01/01/2019',

     @INEND DATE ='12/31/2019'   

    DECLARE @STARTDATE  DATETIME     

    DECLARE @ENDDATE  DATETIME     

         

     IF @INDATE = 4   --Custom  dates

     BEGIN     

       IF @INSTART = ''     

        BEGIN     

          SET @INSTART =DATEADD(YYYY,-1,GETDATE())   

        SET @INEND =GETDATE()   

           

        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     

          ELSE

    IF @INDATE = 5    ---LAst calendar year

      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))   

            

      END    

      ELSE

            

     IF @INDATE = 1     ----Last 12 Months

      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      -----Year to Date

       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      ---Last 24 Months

      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))   

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

     

       

     DECLARE @END_MONTH_YEAR VARCHAR(6)   

       

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

     

        IF LEN(@END_MONTH_YEAR) > 0     

         BEGIN     

          IF CAST(LEFT(@END_MONTH_YEAR,2) AS INT) + 1 = 13     

           BEGIN     

            SET @ENDDATE = (SELECT CAST('1/1/' + CAST((CAST(RIGHT(@END_MONTH_YEAR, 4) AS INT) + 1) AS NVARCHAR(4)) AS DATETIME))     

           END     

          ELSE     

           BEGIN     

            SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEAR,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEAR, 4) AS DATETIME)     

           )     

           END     

         END     

        ELSE     

         BEGIN     

          SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEAR,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEAR, 4) AS DATETIME))     

         END     

       END     

       

     

    SELECT @STARTDATE AS STARTDATE ,@ENDDATE AS ENDDATE

    Friday, August 7, 2020 7:22 AM