How to search between two dates and get all records

Introduction 

In SQL, if you execute your query and it contains between dates and your DateTime has different times, not all relevant data is returned. This is because you didn’t specify the correct time for the date time. The default time is 00:00:00.

Background

This caused me lot of frustration before I figured out how to do it properly. When you choose one date, there are zero records. But if you select the next day you get the record for the previous day.

Using the code 

ID	Name	CapturedDate
1	Susan	2012-03-27 08:02:45
2	John	2012-03-27 09:14:56
3	James	2012-03-27 10:15:45
4	Clair	2012-03-27 11:45:54
5	Blair	2012-03-28 12:45:48

SELECT ID 
FROM TestInfo 
WHERE CapturedDate BETWEEN ‘2012-03-27’ AND ‘2012-03-27’

This query will not return any information, because the default time is 00:00:00. None of the capture dates have a time of 00:00:00. There are different ways to fix this problem.

Solution One

You can type in the correct time.

SELECT ID
FROMTestInfo
WHERE CapturedDate BETWEEN ‘2012-03-27’ AND ‘2012-03-27 23:59:59’

This will return ID 1,2,3,4, because we are searching for all record between midnight and just before midnight of the next day. This solution can work if u knows that u have to add the time.  But sometimes it is impractical to, do this. And this can become a time consuming process. Like when you start using parameters, then the user need to enter the data and to expect the user to add the time can cause a lot of problems.

Solution Two:

You can add the time on to the endDate parameter.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = ‘2012-03-27’
SET @EndDate = ‘2012-03-27’
SELECT ID
FROM  TestInfo
WHERE CapturedDate BETWEEN @StartDate AND @EndDate + ‘ 23:59:59’

This will return ID 1,2,3,4. This solution resolves the issue that the user needs to enter the time.

Solution Three

You can use the dateadd function of SQL.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = ‘2012-03-27’
SET @EndDate = ‘2012-03-27’ 

SELECT ID
FROM  TestInfo
WHERE CapturedDate BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,1,@EndDate))

This will return ID 1,2,3,4.  We are doing a double Dateadd; the first is to add a day to the current endDate, it will be 2012-03-28 00:00:00, then you subtract one second to make the end date 2012-03- 27 23:59:59.