Syntax error converting datetime from character string.
-
I ve written a stored procedure to fetch data from a table. Here i m getting an error "Syntax error converting datetime from character string.". Please guide me. ////////////////////////////////////////////////////////////////////////// CREATE PROCEDURE ic_get_processeddata ( @frmDate DateTime, @toDate DateTime, @empType varchar(20), @ein varchar(15) ) as declare @sqlStr as varchar(255) set @sqlStr ='' set @sqlStr='select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate='+@frmDate+' and toDate='+@toDate if(@empType !='') begin set @sqlStr = @sqlStr + ' and empType=' + @empType end if(@ein !='') begin set @sqlStr = @sqlStr + ' and EIN='+@ein end set @sqlStr = @sqlStr + (' order by empName,empType') print(@sqlStr) exec @sqlStr GO thanx in advance
-
I ve written a stored procedure to fetch data from a table. Here i m getting an error "Syntax error converting datetime from character string.". Please guide me. ////////////////////////////////////////////////////////////////////////// CREATE PROCEDURE ic_get_processeddata ( @frmDate DateTime, @toDate DateTime, @empType varchar(20), @ein varchar(15) ) as declare @sqlStr as varchar(255) set @sqlStr ='' set @sqlStr='select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate='+@frmDate+' and toDate='+@toDate if(@empType !='') begin set @sqlStr = @sqlStr + ' and empType=' + @empType end if(@ein !='') begin set @sqlStr = @sqlStr + ' and EIN='+@ein end set @sqlStr = @sqlStr + (' order by empName,empType') print(@sqlStr) exec @sqlStr GO thanx in advance
Use
CASTorCONVERTto convert@frmDateand@toDatetostring, before you try to concatenate them. -
I ve written a stored procedure to fetch data from a table. Here i m getting an error "Syntax error converting datetime from character string.". Please guide me. ////////////////////////////////////////////////////////////////////////// CREATE PROCEDURE ic_get_processeddata ( @frmDate DateTime, @toDate DateTime, @empType varchar(20), @ein varchar(15) ) as declare @sqlStr as varchar(255) set @sqlStr ='' set @sqlStr='select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate='+@frmDate+' and toDate='+@toDate if(@empType !='') begin set @sqlStr = @sqlStr + ' and empType=' + @empType end if(@ein !='') begin set @sqlStr = @sqlStr + ' and EIN='+@ein end set @sqlStr = @sqlStr + (' order by empName,empType') print(@sqlStr) exec @sqlStr GO thanx in advance
Wouldn't this be a better solution: CREATE PROCEDURE ic_get_processeddata ( @frmDate DateTime, @toDate DateTime, @empType varchar(20), @ein varchar(15) ) AS SELECT EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus FROM ic_process WHERE frmDate = @frmDate AND toDate = @toDate AND (empType = @empType OR @empType = '') AND (EIN = @ein OR @ein='') ORDER by empName,empType This way the @toDate remains a datetime type.
-
Wouldn't this be a better solution: CREATE PROCEDURE ic_get_processeddata ( @frmDate DateTime, @toDate DateTime, @empType varchar(20), @ein varchar(15) ) AS SELECT EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus FROM ic_process WHERE frmDate = @frmDate AND toDate = @toDate AND (empType = @empType OR @empType = '') AND (EIN = @ein OR @ein='') ORDER by empName,empType This way the @toDate remains a datetime type.
Hi szukuro Thanx for your suggestion. But your tric was not working for my solution. Some how i modified my stored procedure. Is this the correct way?? can we optimized it?? CREATE PROCEDURE ic_get_processeddata ( @frmDate DateTime, @toDate DateTime, @empType varchar(20), @ein varchar(15) ) as if(@empType !='') and (@ein !='') begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and empType=@empType and ein=@ein order by empType,empName end else if (@empType !='') and (@ein ='') begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and empType=@empType order by empName,empType end else if(@ein !='') and (@empType ='') begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and ein=@ein order by empName,empType end else begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate order by empName,empType end GO
-
Hi szukuro Thanx for your suggestion. But your tric was not working for my solution. Some how i modified my stored procedure. Is this the correct way?? can we optimized it?? CREATE PROCEDURE ic_get_processeddata ( @frmDate DateTime, @toDate DateTime, @empType varchar(20), @ein varchar(15) ) as if(@empType !='') and (@ein !='') begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and empType=@empType and ein=@ein order by empType,empName end else if (@empType !='') and (@ein ='') begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and empType=@empType order by empName,empType end else if(@ein !='') and (@empType ='') begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate and ein=@ein order by empName,empType end else begin select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate=@frmDate and toDate=@toDate order by empName,empType end GO