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.
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.
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.
You need to be more explicit and add the start and end times as well, down to the milliseconds:
The database can very well interpret
'2012-10-27'
as'2012-10-27 00:00:00.000'
.The unambiguous way to write this is (i.e. increase the 2nd date by 1 and make it
<
)If you're using SQL Server 2008 or above, you can safety CAST as DATE while retaining SARGability, e.g.
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:
As others have answered, you probably have a
DATETIME
(or other variation) column and not aDATE
datatype.Here's a condition that works for all, including
DATE
:@Aaron Bertrand has blogged about this at: What do
BETWEEN
and the devil have in common?try to use following query