Selecting records between two dates nothing show although it have records in database why

[ahm1985]

I have database for hr have Employee table
Employee table has JoinDate with datatype datetime
I need to make dynamic search so that i make dynamic stored procedure

ViewEmployee23

SELECT CONVERT(varchar, DriverID) AS EmployeeID,
CONVERT(varchar, dbo.Employee.JoinDate, 103) AS JoinDate, CONVERT(varchar, dbo.Employee.ResignDate, 103) AS ResignDate
FROM dbo.Employee
and Stored procedure like following :
ALTER Procedure [dbo].[sp_EmployeeSelect5]
@JoinDate nvarchar(20)
@StartDate nvarchar(20)
@EndDate nvarchar(20)
as
Declare @SQLQuery as nvarchar(2000)
SET @SQLQuery ='SELECT * from ViewEmployee23 Where (1=1)'
IF @StartDate <> ''
SET @SQLQuery = @SQLQuery + ' AND (JoinDate <= '''+ @StartDate +''') '
IF @EndDate <> ''
SET @SQLQuery = @SQLQuery + ' AND (JoinDate <= '''+ @EndDate +''') '

When i test query in query analzer i do following
select * from ViewEmployee23 where JoinDate>='01/01/2014' and JoinDate<='01/04/2014'
it show nothing
select * from dbo.Employee where JoinDate>='01/01/2014' and JoinDate<='01/04/2014'
it show nothing
select * from dbo.Employee where JoinDate>='2014/01/01' and JoinDate<='2014/04/01'
it show one record exist and this is acctually true result
Now how i get records and filter between two dates fromdate todate based on formate
dd/mm/yyyy in dynamic stored procedure search

what i change

 

[Olaf Doschke]

http://msdn.microsoft.com/de-de/library/ms187819.aspx

SET DATEFORMAT determines how date values are interpreted.

I suggest you either change your code to use datetime variables instead of nvarchar or you use a format, which does NOT depend on the DATEFORMAT setting. You already found out one YYYY/MM/DD,but you best not neither slashes, hyphens not periods, but simply YYYYMMDD, that works independent of any regional or language or date setting.

Bye, Olaf.

 

[ahm1985]

Thank you for reply
using dateformate in which place