INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

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

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

(OP)
Thank you for reply
using dateformate in which place

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close