conversion faild when converting date time from charachter string error

[ahm1985]

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

 

[bborissov]

When you datetime to strings you must cast them to string:

Create proc searchData 
@StartDate datetime,
@EndDate datetime

AS
BEGIN
   SET NOCOUNT ON;
   Declare @SQLQuery as nvarchar(2000)
   SET @SQLQuery ='SELECT * from Employee'

   If (@StartDate is not NULL) AND (@EndDate is not NULL)
      BEGIN
          Set @SQLQuery = @SQLQuery + ' WHERE JoinDate 
                                     BETWEEN '+ CONVERT(????,@StartDate,?????) +' AND '+CONVERT(????,@StartDate,?????)+')'
          Exec (@SQLQuery)
     END
END

or use sp_executesql and pass them as parameters:

Create proc searchData 
@StartDate datetime,
@EndDate datetime

AS
BEGIN
   SET NOCOUNT ON;
   Declare @SQLQuery      as nvarchar(2000)
   SET @SQLQuery ='SELECT * from Employee'

   If (@StartDate is not NULL) AND (@EndDate is not NULL)
      BEGIN
          Set @SQLQuery = @SQLQuery + ' WHERE JoinDate BETWEEN @StartDate AND @EndDate'
     END
     EXECUTE sp_executesql @SQLQuery, 
                          N'@StartDate datetime, @EndDate datetime',
                          @StartDate = @StartDate, @EndDate = @EndDate
END

Borislav Borissov
VFP9 SP2, SQL Server

 

[gk53]

Why all that code with sp_executesql?
Why not simple

Create procedure dbo.searchData 
@StartDate datetime = '1/1/1900',
@EndDate datetime = '1/1/1900'

AS
SELECT * from Employee
where JoinDate  between @StartDate and @EndDate

 

[bborissov]

Because when one or both dates are NULL ahm185 wants to get ALL records,
bu you are right, you always can set default value of the @EndDate to 31 Dec 9999

Borislav Borissov
VFP9 SP2, SQL Server

 

[gk53]

not 31/12/9999 it will be error but
12/31/3999 will work
so

Create procedure dbo.searchData 
@StartDate datetime = '1/1/1900',
@EndDate datetime = '12/31/3999'

AS
SELECT * from Employee
where JoinDate  between @StartDate and @EndDate

 

[bborissov]

http://msdn.microsoft.com/en-us/library/ms187819.aspx

Date range January 1, 1753, through December 31, 9999

Borislav Borissov
VFP9 SP2, SQL Server