conversion faild when converting date time from charachter

Posted by Ahmedsa under C# on 9/1/2014 | Points: 10 | Views : 539 | Status : [Member] | Replies : 2
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




Responses

Posted by: Bandi on: 9/1/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
The Problem with above SP is that you are concatenating string data with datetime type.... 
--Simulating above issue
SELECT 'string ' + GETDATE() + ' throws error'


-- Alternate to resolve the above issue is

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 '+ convert(varchar,@StartDate , 103) +' AND '+ convert(varchar,@EndDate , 103)+')'
Exec (@SQLQuery)
GO


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

As Bandi suggested that you are trying to concatenate datetime field with string result that is the reason it's throwing this error. If you want to resolve this you need to convert the date field into varchar field then you can perform your task..

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response