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).
This was first published in July 2008