How to use DATETime Column in dynamic query in sql server?

Question

I have a Stored Proc which is using for Search Applicants is written as below:

 /*  
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()) >= '+@AgeFrom
end
 if @AgeTo!=0
 begin
SET @SQL+=' AND DATEDIFF(YEAR,tblApplicants.DateOfBirth, GETDATE()) <= '+@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 > @FirstRec AND RowNum < @LastRec   
RETURN            
END

i want to apply "=>" and "<=" operators on ApplyDate. every time i got "*Conversion failed when converting date and/or time from character string. *"

please help me how can apply these operators on ApplDate

try replacing CONVERT(DATETIME,tblApplicants.ApplyDate,101) ='+ CONVERT(DATETIME,@ApplyDateFrom,101) with 'DATEDIFF(DD,tblApplicants.ApplyDate, ' + @ApplyDateFrom + ') = 0' — vikas
– vikas, Commented Oct 1, 2013 at 10:24

Ram Singh · Accepted Answer · 2013-10-01 11:08:31Z

2

SET @SQL+= ' AND CONVERT(DATETIME,tblApplicants.ApplyDate,101) ='+ CONVERT(DATETIME, @ApplyDateFrom, 101)

change it to:

SET @SQL+= ' AND CONVERT(DATETIME, tblApplicants.ApplyDate, 101) = CONVERT(DATETIME, ''' + cast(@ApplyDateFrom as nvarchar) + ''', 101)'

edited Oct 1, 2013 at 11:08

Ram Singh

6,97235 gold badges107 silver badges172 bronze badges

answered Oct 1, 2013 at 10:38

Ren

4374 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vikas · Accepted Answer · 2013-10-01 10:48:03Z

1

Replace this line

AND CONVERT(DATETIME,tblApplicants.ApplyDate,101) ='+ CONVERT(DATETIME,@ApplyDateFrom,101)

Updated

AND DATEDIFF(DD,tblApplicants.ApplyDate, CAST(''' + CAST(@ApplyDateFrom as varchar) + ''' as datetime)) = 0

for more look this query

DECLARE @ApplyDateFrom  DATETIME='2010-06-29 00:00:00.000'
DECLARE @QUERY varchar(max)
SET @QUERY =
'SELECT  DATEDIFF(DD,GETDATE(), CAST(''' + CAST(@ApplyDateFrom as varchar) + ''' as datetime))'
PRINT @QUERY

edited Oct 1, 2013 at 10:48

answered Oct 1, 2013 at 10:40

vikas

2,8224 gold badges29 silver badges37 bronze badges

Comments

podiluska · Accepted Answer · 2013-10-01 10:27:56Z

0

@AgeFrom is an int. You need to convert it to a varchar before concatentating it to a string.

  'AND DATEDIFF(YEAR,tblApplicants.DateOfBirth, GETDATE()) >= '+convert(varchar(5),@AgeFrom)

Not that this will work, of course, as datediff(year... won't give you an age. eg:

 select DATEDIFF(YEAR,'2000-12-25','2013-10-01')

will return 13, not 12.

Similarly for ApplyDate convert that to a string

  CONVERT(DATE,tblApplicants.ApplyDate,101) = ''' + CONVERT(varchar(20),@ApplyDateFrom,111) + ''''

To be honest though, this whole approach looks like a bad idea to me.

edited Oct 1, 2013 at 10:27

answered Oct 1, 2013 at 10:20

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

1 Comment

Ram Singh Over a year ago

please i want to apply the check for "Apply Date", please suggest me for that particular column

tsohtan · Accepted Answer · 2013-10-01 10:45:29Z

0

you just need to make sure the parameter before it come into your stored procedured is in this format yyyy-MM-dd HH:mm:ss

then you're save to compare using "<=" and ">=" and doesn't need to convert it to other format just compare like this

tblApplicants.ApplyDate >='2013-10-01 18:00:00'

You need to cast applydate to string before concatenate to dynamics sql.

Mean SET @SQL+= ' AND CONVERT(DATETIME,tblApplicants.ApplyDate,101) ='+ cast (CONVERT(DATETIME,@ApplyDateFrom,101) AS VARCHAR)

edited Oct 1, 2013 at 10:45

answered Oct 1, 2013 at 10:25

tsohtan

8502 gold badges13 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to use DATETime Column in dynamic query in sql server?

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related