SQL Server : fetching records between two dates?

17

In SQL I write a SELECT statement to fetch data between two dates, using between and

Ex:

select * 
from xxx 
where dates between '2012-10-26' and '2012-10-27'

But the rows returned are for 26th only, not 26th and 27th.

Can you help me? Thank you.

This question is tagged with sql-server datetime date-range

~ Asked on 2012-10-27 10:33:12

The Best Answer is


14

As others have answered, you probably have a DATETIME (or other variation) column and not a DATE datatype.

Here's a condition that works for all, including DATE:

SELECT * 
FROM xxx 
WHERE dates >= '20121026' 
  AND dates <  '20121028'    --- one day after 
                             --- it is converted to '2012-10-28 00:00:00.000'
 ;

@Aaron Bertrand has blogged about this at: What do BETWEEN and the devil have in common?

~ Answered on 2012-10-27 11:00:37


13

You need to be more explicit and add the start and end times as well, down to the milliseconds:

select * 
from xxx 
where dates between '2012-10-26 00:00:00.000' and '2012-10-27 23:59:59.997'

The database can very well interpret '2012-10-27' as '2012-10-27 00:00:00.000'.

~ Answered on 2012-10-27 10:35:19


Most Viewed Questions: