September 1, 2014 at 1:27 am
Hi guy when i run this Stored procedure it give me message error
conversion faild when converting date time from charachter sting
stored procedure as following
Create proc searchData
@StartDate datetime,
@EndDate datetime
as
Declare @sqlquery as nvarchar(2000)
SET @sqlquery ='SELECT * from Employee Where (1=1)'
If (@StartDate is not NULL) AND (@EndDate is not NULL)
Set @sqlquery = @sqlquery + ' And (JoinDate
BETWEEN '+ @StartDate +' AND '+@EndDate+')'
Exec (@SQLQuery)
JoinDate found in table Employee as datetime
but when i make stored procedure as following
it work in formate dd/mm/yyyy and this is what i need
ALTER proc [dbo].[searchData]
@StartDate datetime
@EndDate datetime,
as
select * from dbo.Employee e where JoinDate between @StartDate and @EndDate
Now what is the proplem in first stored procedure
Please help me if possible
September 1, 2014 at 4:56 am
There are 2 problems with your code. You cannot concat datetime data type with varchar data type, so you need to convert your parameters to varchar data type (using convert or cast functions). The second problem is that you need to put apostrophes around the dates. The code bellow shows how to do that. Notice also that your code has the potential to be vulnerable to SQL injection
declare @StartDate datetime = '20000101'
declare @EndDate datetime = '20140901'
declare @sqlquery varchar(200)
set @sqlquery = 'select * from sys.objects where create_date > '''
+ cast(@StartDate as varchar(20)) + ''' AND create_date < ''' + cast(@EndDate as varchar(20))+ ''''
select @sqlquery
exec(@SQLQuery)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply