>>Otherwise there is a risk of losing some values due to time precision.
>>
>>Aaron Bertrand's blog (short one) explains it very clearly - take a read, you would not regret.
>
>You once again, constructing a sentence that is impossible for me to understand. I said I wouldn't spend time reading, just show me the lines where it supports your claim that SQL server datetime fields lose their time portions when retrieved using Between.
>Cetin
-------------------
Ok, I'll quote for you:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Another approach users take is:

SELECT COUNT(*)
FROM dbo.SomeLogTable
WHERE DateColumn BETWEEN '20091011' AND '20091012';
Well, this approach is okay, as long as you don't have any rows that fall on midnight at the upper bound - which can be much more common if parts of your application strip time from date/time values. In that case, this query will include data from the next day; not exactly what was intended. In some cases, that *is* what is intended: some people think the above query should return all the rows from October 11th, and also all the rows from October 12th. Remember that this query can be translated to one of the following, without changing the meaning:
SELECT COUNT(*)
FROM dbo.SomeLogTable
WHERE DateColumn BETWEEN '2009-10-11T00:00:00.000' AND '2009-10-12T00:00:00.000';
-- or

SELECT COUNT(*)
      FROM dbo.SomeLogTable
      WHERE DateColumn >= '2009-10-11T00:00:00.000' AND DateColumn <= '2009-10-12T00:00:00.000';

(Note that in the second example, that is greater than or equal to the first variable and less than or equal to the second variable.) This means that you will return rows from October 12th at exactly midnight, but not at 1:00 AM, or 4:00 PM, or 11:59 PM.

Then the user tries this, so they can still use BETWEEN and save a few key strokes:

SELECT COUNT(*)
      FROM dbo.SomeLogTable
      WHERE DateColumn BETWEEN '20091011' AND '2009-10-11T23:59:59.997';

-- or

SELECT COUNT(*)
      FROM dbo.SomeLogTable
      WHERE DateColumn BETWEEN '20091011' AND DATEADD(SECOND, -1, '20091012');

These are no good either. If the data type of the column is SMALLDATETIME, the comparison is going to round up, and you *still* might include data from the next day. For the second version, if the data type of the column is DATETIME, there is still the possibility that you are going to miss rows that have a time stamp between 11:59:59 PM and 11:59:59.997 PM. Probably not many, but if there is even one, your data is no longer accurate.