title: How to use DATETime Column in dynamic query in sql server?
body: <p>I have a Stored Proc which is using for Search Applicants is written as below:</p>
  
  <pre><code> /*  
  AUTHOR   :   
  CREATION DATE  :      
  NOTES   :  
  PURPOSE   :   
  MODIFIED BY  :  
  MODIFICATION DATE :  
  */  
  ALTER PROCEDURE USP_GET_ApplicantByFilter  
  (  
   @ApplicantName  VARCHAR(100)='Ram',  
   @AgeFrom  INT=0,  
   @AgeTo   INT=0,  
   @Experience  INT=0,  
   @ExperienceMonths  INT=0,  
   @City   VARCHAR(100)='',  
   @QualificationID INT=0,  
   @PositionID  INT=0,  
   @ApplyDateFrom  DATETIME='2010-06-29 00:00:00.000',  
   @ApplyDateTo  DATETIME=NULL,
   @SortColumn Varchar(128)='ApplicantID',  
  @SortDirection Varchar(56)='desc',  
  @Page int=1,  
  @RecsPerPage int   =10
  )  
  AS
  DECLARE @SQL VARCHAR(MAX)                              
  DECLARE @DSQL VARCHAR(MAX)  
  DECLARE @whereCondition VARCHAR(1024)                              
  DECLARE @FirstRec int, @LastRec int                              
  SET @FirstRec = (@Page - 1) * @RecsPerPage                              
  SET @LastRec = (@Page * @RecsPerPage + 1)  
  Declare @SectionCount int;  
  Set NoCount On  
  Begin  
   SET @SQL='Select  ROW_NUMBER() over( order by '+@SortColumn + ' ' +@SortDirection +') rownum, tblApplicants.ApplicantID, tblApplicants.ApplicantName, tblApplicants.FatherName, tblApplicants.DateOfBirth,  tblApplicants.QualificationID,  tblApplicants.EMailID, tblApplicants.Address, tblApplicants.City, tblApplicants.State, tblApplicants.Phone,
   tblApplicants.ApplyDate, tblApplicants.PositionID, tblApplicants.isActive,  tblPositionMaster.PositionName 
  FROM tblApplicants INNER JOIN tblPositionMaster ON tblApplicants.PositionID = tblPositionMaster.PositionID 
  WHERE 1=1 AND tblApplicants.isActive=1  '
  if @ApplicantName!=''
  begin
    SET @sql +=' AND tblApplicants.ApplicantName like ''%'+ @ApplicantName +'%'''
  end
  if @AgeFrom!=0
  begin
  SET @SQL+=' AND DATEDIFF(YEAR,tblApplicants.DateOfBirth, GETDATE()) &gt;= '+@AgeFrom
  end
   if @AgeTo!=0
   begin
  SET @SQL+=' AND DATEDIFF(YEAR,tblApplicants.DateOfBirth, GETDATE()) &lt;= '+@AgeTo
   end
  if @ApplyDateFrom IS NOT NULL
  begin
  SET @SQL+= ' AND CONVERT(DATETIME,tblApplicants.ApplyDate,101) ='+ CONVERT(DATETIME,@ApplyDateFrom,101)
  end
  SET @DSQL ='SELECT  * from (' + @SQL +') AS tbl'                              
    print @DSQL  
  DECLARE @TEMPResult TABLE(RowNum INT,       
     ApplicantID int,                
  ApplicantName varchar(100),  
  FatherName varchar(200),  
  DateOfBirth DATETIME,  
  QualificationID int,  
  EMailID varchar(200),  
  Address varchar(200),  
  City varchar(200),  
  State varchar(200),  
  Phone varchar(200),  
  ApplyDate DATETIME,
  PositionID int,
  isActive int,
  PositionName varchar(200)
  )
       INSERT INTO @TEMPResult EXEC(@DSQL)  
  SELECT (Select Count(*) from @TEMPResult) as Count, * FROM @TEMPResult WHERE RowNum &gt; @FirstRec AND RowNum &lt; @LastRec   
  RETURN            
  END
  </code></pre>
  
  <p>i want to apply "<strong>=></strong>" and "<strong>&lt;=</strong>" operators on ApplyDate. every time i got "<strong>*<em>Conversion failed when converting date and/or time from character string.</strong>
  *</em>"</p>
  
  <p>please help me how can apply these operators on ApplDate</p>