EXPERT RESPONSE
You may not have given enough information for me to diagnose your
problem correctly. BETWEEN does include the
endpoints of the range. This is the default behaviour.
What might be happening is that you actually have datetime
values in your table, rather than date values.
For example, let's say the values in your table are:
myDateTime
2008-01-01 01:01
2008-01-22 01:22
2008-02-01 02:01
2008-02-29 02:29
2008-03-30 03:30
2008-03-31 09:37
Now run a query with this condition:
WHERE myDateTime BETWEEN '2008-01-01'
AND '2008-03-31'
You will see that the last value, 2008-03-31 09:37, is missing.
This is because it is outside the range specified
by the BETWEEN condition. The reason is due to fact that the values
in the BETWEEN condition are converted to datetime values in
order to do the comparisons, so the condition is actually:
WHERE myDateTime BETWEEN '2008-01-01 00:00:00'
AND '2008-03-31 00:00:00'
Thus, all the datetime values for that last day will be missing (except
for those, if any, which specify midnight exactly).
When dealing with datetimes like this, you could try
to specify the upper end of the range explicitly, like this:
WHERE myDateTime BETWEEN '2008-01-01'
AND '2008-03-31 23:59:59.993'
However, the problem with this is that you have to be sure you specify
the actual last possible datetime value for that date, and this becomes
quite tricky as it depends on which version of which DBMS you're using.
A much better approach is to abandon the BETWEEN condition, and do this
instead:
WHERE myDateTime >= '2008-01-01'
AND myDateTime < '2008-04-01'
Notice that the operator in the second condition is less than, not less
than or equal.
The bonus here is that you don't have to waste any time calculating
the last day of the month (28th, 29th, 30th, or 31st). Just make it
less than the 1st of the following month. Easier to code, just as efficient,
and guaranteed to get all included datetime values no matter what
precision the database uses (microsecond, 3 microseconds, whatever).
Neat, eh?
|
