title: Stored Procedure Operand type clash: date is incompatible with int
body: <p>This is my stored procedure</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_ListFirmJobFilter]
      (@Status as nvarchar(10),
       @CityID as nvarchar(10),
       @DataStart as date,
       @DataEnd as date,
       @FirmID as nvarchar(10)
  )
  AS
  BEGIN
      DECLARE @Sql NVARCHAR(MAX) 
      SET @Sql = N'SELECT * FROM Jobs WHERE 1=1'
  
      IF @Status IS NOT NULL
      BEGIN
          SET @Sql += ' AND Status = '+@Status+''
      END
  
      IF @CityID IS NOT NULL
      BEGIN
          SET @Sql += ' AND CityID = '+@CityID+''
      END
  
      IF @FirmID IS NOT NULL
      BEGIN
          SET @Sql += ' AND FirmID = '+@FirmID+' '
      END
  
      IF @DataStart IS NOT NULL
      BEGIN
          SET @Sql += ' AND (InsertedDate &gt;= ' + CONVERT(VARCHAR(50), @DataStart)
          SET @Sql += ' AND InsertedDate &lt;= ' + CONVERT(VARCHAR(50), @DataEnd)
          SET @Sql += ')'
      END 
  
      PRINT(@Sql)
      EXEC(@Sql)
  END
  </code></pre>
  
  <p>The command completed successfully.</p>
  
  <p>But when I execute that stored procedure with that parameter </p>
  
  <pre><code>DECLARE @return_value int
  
  EXEC @return_value = [dbo].[sp_ListFirmJobFilter]
                          @Status = NULL,
                          @CityID = null,
                          @DataStart = N'2016-06-26',
                          @DataEnd = N'2016-06-28',
                          @FirmID = 4
  
  SELECT  'Return Value' = @return_value
  GO
  </code></pre>
  
  <p>I get an error:</p>
  
  <blockquote>
    <p>Operand type clash: date is incompatible with int  </p>
  </blockquote>
  
  <p>I look for executed SQL command (Print SQL string). This is executed command: </p>
  
  <pre><code>SELECT * 
  FROM Jobs 
  WHERE 1=1 
    AND FirmID = 4  
    AND (InsertedDate &gt;= 2016-06-26 AND InsertedDate &lt;= 2016-06-28)
  </code></pre>
  
  <p>When I execute that it give same error. I changed it to:</p>
  
  <pre><code>SELECT * 
  FROM Jobs 
  WHERE 1=1 
    AND FirmID = 4  
    AND (InsertedDate &gt;= '2016-06-26' AND InsertedDate &lt;= '2016-06-28')
  </code></pre>
  
  <p>this worked.</p>
  
  <p>What should I do? <code>InsertedDate</code> type is date </p>