[sql-server] SQL Server : fetching records between two dates?

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.

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

try to use following query

select * 
from xxx 
where convert(date,dates) >= '2012-10-26' and convert(date,dates) <= '2012-10-27'

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?

The unambiguous way to write this is (i.e. increase the 2nd date by 1 and make it <)

select * 
from xxx 
where dates >= '20121026'
  and dates <  '20121028'

If you're using SQL Server 2008 or above, you can safety CAST as DATE while retaining SARGability, e.g.

select * 
from xxx 
where CAST(dates as DATE) between '20121026' and '20121027'

This explicitly tells SQL Server that you are only interested in the DATE portion of the dates column for comparison against the BETWEEN range.

Try this:

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

Your question didnt ask how to use BETWEEN correctly, rather asked for help with the unexpectedly truncated results...

As mentioned/hinting at in the other answers, the problem is that you have time segments in addition to the dates.

In my experience, using date diff is worth the extra wear/tear on the keyboard. It allows you to express exactly what you want, and you are covered.

select * 
from xxx 
where datediff(d, '2012-10-26', dates) >=0
and  datediff(d, dates,'2012-10-27') >=0

using datediff, if the first date is before the second date, you get a positive number. There are several ways to write the above, for instance always having the field first, then the constant. Just flipping the operator. Its a matter of personal preference.

you can be explicit about whether you want to be inclusive or exclusive of the endpoints by dropping one or both equal signs.

BETWEEN will work in your case, because the endpoints are both assumed to be midnight (ie DATEs). If your endpoints were also DATETIME, using BETWEEN may require even more casting. In my mind DATEDIFF was put in our lives to insulate us from those issues.