Solved

Between in SQL Server Stored Procedure Problem

Posted on 2003-12-09
5
650 Views
Last Modified: 2008-02-26
Here is my stored procedure:

CREATE PROCEDURE summary
      @startdate varchar(20),
      @enddate varchar(20)
AS
select * from  SanderGroup
where Entrydate between CONVERT(varchar, CONVERT(smalldatetime, @startdate)) and  CONVERT(varchar, CONVERT(smalldatetime, @enddate))
GO

I was under the understanding that between is inclusive, so if I used 11/03/03 as my startdate and 11/05/03 as my end date I should get records with 11/03/03, 11/04/03, AND 11/05/03

But I don't I only get 3, and 4.  Am I missing something?

TIA
0
Comment
Question by:Tigger996
5 Comments
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 33 total points
ID: 9910472
Yes
you could use this code instead

CREATE PROCEDURE summary
     @startdate varchar(20),
     @enddate varchar(20)
AS
select * from  SanderGroup
where Entrydate >= CONVERT(varchar, CONVERT(smalldatetime, @startdate)) and  EntryDate < dateadd(d, 1, CONVERT(varchar, CONVERT(smalldatetime, @enddate)))

Dates whithout time part are translated to 0:00 AM

between 11/03/03 0:00 PM and 11/05/03 0:00 PM
will fail to return events that occured on 11/05/03 after 0:00 PM

Hilaire

0
 
LVL 8

Assisted Solution

by:dishanf
dishanf earned 33 total points
ID: 9910543
try this..
CREATE PROCEDURE summary
     @startdate varchar(20),
     @enddate varchar(20)
AS
select * from  Agent
where CreationDate between CONVERT(varchar(8), @startdate,101) and  CONVERT(varchar(8), @enddate,101)
GO
0
 
LVL 8

Expert Comment

by:dishanf
ID: 9910549
table names must be change like this..

select * from  SanderGroup
where Entrydate between CONVERT(varchar(8), @startdate,101) and  CONVERT(varchar(8), @enddate,101)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 34 total points
ID: 9912550
You need the starting time to be midnight and the ending time to 23:59 (for SMALLDATETIME; 23:59:59.997 for DATETIME).  For example:

select * from  SanderGroup
where Entrydate between CAST(CONVERT(varchar(10), @startdate, 120) AS SMALLDATETIME) and CAST(CONVERT(varchar(10), @enddate, 120) + ' 23:59' AS SMALLDATETIME)
GO


If is safer to use format 120, which is always recognized correctly regardless of server settings, and it allows a time to be specified, which 101 does not.   The VARCHAR(10) on the CONVERT will truncate the time portion, leaving only the date (yyyy-mm-dd).
0
 

Author Comment

by:Tigger996
ID: 9923454
Thanks for the suggestions.  Points for all since all methods work.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 38
sql server computed columns 11 31
optimize stored procedure 6 25
Help in Bulk Insert 9 33
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question