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