I have database for hr have Employee tableEmployee table has JoinDate with datatype datetimeI 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)asDeclare @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 followingselect * 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 resultNow how i get records and filter between two dates fromdate todate based on formatedd/mm/yyyy in dynamic stored procedure search
what i change