Click here to Skip to main content
15,749,184 members
Articles / Database Development / SQL Server / SQL Server 2008
Posted 27 Mar 2012



How to search between two dates and get all records

Rate me:
Please Sign up or sign in to vote.
4.50/5 (4 votes)
27 Mar 2012CPOL2 min read
Return all records between two dates that include time in SQL.


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.


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

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

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer
South Africa South Africa
This member doesn't quite have enough reputation to be able to display their biography and homepage.

Comments and Discussions

AnswerHow to get data between two dates from SQL Server Pin
Narendra Singh Chauhan11-Jun-15 23:12
Narendra Singh Chauhan11-Jun-15 23:12 
SuggestionMessage Removed Pin
17-Sep-12 21:41
KrishanGahlot17-Sep-12 21:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.