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

Asked By ahmed salah on 30-Aug-14 12:30 PM

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


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)
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

Robbe Morris replied to ahmed salah on 30-Aug-14 03:08 PM
SET @SQLQuery = @SQLQuery + ' AND (JoinDate <= '''+ @StartDate +''') '

should be

SET @SQLQuery = @SQLQuery + ' AND (JoinDate >= '''+ @StartDate +''') '

On a side note, this code is vulnerable to sql injection attacks and should never be allowed into production.  To do dynamic searches, write code in your application that is dynamic and dynamically add parameter objects to your ADO or ADO.NET code (or whatever your application is written in).  This will remove the possibility of sql injection vulnerabilities.

You should always do everything possible to avoid dynamic sql inside stored procedures because it is the most difficult place to plug security holes.