Why does my query search datetime not match?

select * 
from A 
where posted_date >= '2015-07-27 00:00:00.000' 
  and posted_date  <= '2015-07-27 23:59:59.999'

But the result contains a record that has posted_date today: 2015-07-28.
My database server is not in my country.
What is the problem ?

As several others have mentioned in comments and other answers to your question the core issue is 2015-07-27 23:59:59.999 is being rounded to 2015-07-28 00:00:00.000 by SQL Server. Per the documentation for DATETIME:


Time range - 00:00:00 through 23:59:59.997

Note that the time range can never be .999. Further down in the documentation it specifies the rounding rules that SQL Server uses for the least significant digit.

Notice that the least significant digit can only have one of three potential values: "0", "3", or "7".

There are several solutions/workarounds for this that you can use.

-- Option 1
SELECT 
    * 
FROM A 
WHERE posted_date >= '2015-07-27 00:00:00.000' 
  AND posted_date = '2015-07-27 00:00:00.000' 
  AND posted_date <=  '2015-07-27 23:59:59.997' --Round down and keep equality

-- Option 3
SELECT 
    * 
FROM A 
WHERE CAST(posted_date AS DATE) = '2015-07-27' -- Use different data type

-- Option 4
SELECT 
    * 
FROM A 
WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' -- Cast to string stripping off time

-- Option 5
SELECT 
    * 
FROM A 
WHERE posted_date BETWEEN '2015-07-27 00:00:00.000' 
  AND '2015-07-27 23:59:59.997' --Use between

Out of the five options I've presented above I would consider options 1 and 3 the only viable options. They convey your intent clearly, and aren't going to break if you update data types. If you are using SQL Server 2008 or newer I think option 3 should be your preferred approach. That is especially true if you can change away from using the DATETIME datatype to a DATE data type for your posted_date column.

Regarding option 3, a very good explanation about some issues can be found here: Cast to date is sargable but is it a good idea?

I don't like options 2 and 5 because the .997 fractional seconds is going to be just another magic number that people are going to want to "fix." For some more reasons why BETWEEN isn't widely embraced you might want to checkout this post.

I don't like option 4 because converting data types to a string for comparison purposes feels dirty to me. A more qualitative reason to avoid it in SQL Server is it impacts sargability aka you can't perform an index seek and that will frequently result in poorer performance.

For more information on the right way and wrong way to handle date range queries checkout this post by Aaron Bertrand.

In parting you would be able to keep your original query and it would behave as desired if you change your your posted_date column from a DATETIME to a DATETIME2(3). That would save storage space on the server, give you greater accuracy at the same precision, be more standards compliant/portable, and allow you to easily adjust the accuracy/precision if your needs change in the future. However, this is only an option if you are using SQL Server 2008 or newer.

As a bit of trivia the 1/300 of a second accuracy with DATETIME seems to be a hold over from UNIX per this StackOverflow answer. Sybase which has a shared heritage has a similar 1/300 of a second accuracy in their DATETIME and TIME data types but their least significant digits are a touch different at "0", "3", and "6". In my opinion the 1/300 of a second and/or 3.33ms accuracy is an unfortunate architectural decision since the 4 byte block for the time in SQL Server's DATETIME data type could have easily supported 1ms accuracy.

StackExchange Database Administrators Q#108287, answer score: 19