Selecting records between two dates nothing show although it have records in database why
Selecting records between two dates nothing show although it have records in database why
(OP)
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
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
Talk To Other Members
RE: Selecting records between two dates nothing show although it have records in database why
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.
RE: Selecting records between two dates nothing show although it have records in database why
using dateformate in which place