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 ?
Since you are using datetime datatype, you need to understand how sql server rounds datetime data.
╔═══════════╦═════╦═════════════════════════════╦═════════════════════════════╦══════════╦═══════════╗
║ Name ║ sn ║ Minimum value ║ Maximum value ║ Accuracy ║ Storage ║
╠═══════════╬═════╬═════════════════════════════╬═════════════════════════════╬══════════╬═══════════╣
║ datetime ║ dt ║ 1753-01-01 00:00:00.000 ║ 9999-12-31 23:59:59.997 ║ 3.33 ms ║ 8 bytes ║
║ datetime2 ║ dt2 ║ 0001-01-01 00:00:00.0000000 ║ 9999-12-31 23:59:59.9999999 ║ 100ns ║ 6-8 bytes ║
╚═══════════╩═════╩═════════════════════════════╩═════════════════════════════╩══════════╩═══════════╝
Using below query, you can easily see the problem of rounding that sql server does when you use DATETIME data type.
select '2015-07-27 00:00:00.000' as Original_startDateTime,
convert(datetime ,'2015-07-27 00:00:00.000') as startDateTime,
'2015-07-27 23:59:59.999' as Original_endDateTime,
convert(datetime ,'2015-07-27 23:59:59.999') as endDateTime,
'2015-07-27 00:00:00.000' as Original_startDateTime2,
convert(datetime2 ,'2015-07-27 00:00:00.000') as startDateTime2, -- default precision is 7
'2015-07-27 23:59:59.999' as Original_endDateTime2,
convert(datetime2 ,'2015-07-27 23:59:59.999') as endDateTime2 -- default precision is 7
DATETIME2 has been around since SQL Server 2008, so start using it instead of DATETIME. For your situation, you can use datetime2 with precision of 3 decimals e.g. datetime2(3).
Benefits of Using datetime2:
datetime supporting only 3 decimal places .. and hence you see the rounding issue since by default datetime rounds the nearest .003 seconds with increments of .000, .003 or .007 seconds.datetime2 is much more precise than datetime and datetime2 gives you control of DATE and TIME as opposed to datetime.Reference :
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-28 00:00:00.000' --Round up and remove equality
-- Option 2
SELECT
*
FROM A
WHERE 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.
Implicit Conversion
I supposed posted_date data type is Datetime. However it does not matter whether the type on the other side is Datetime, Datetime2 or just Time because the string (Varchar) will be implicitly converted to Datetime.
With posted_date declared as a Datetime2 (or Time), the posted_date <= '2015-07-27 23:59:59.99999' where clause fails because altough 23:59:59.99999 is a valid Datetime2 value, this is not a valid Datetime value:
Conversion failed when converting date and/or time from character string.
Time Range for Datetime
The time range of Datetime is 00:00:00 through 23:59:59.997. Therefore 23:59:59.999 is out of range and has to be round up or down to the closest value.
Accuracy
Besides Datetime values are rounded by increments of .000, .003, or .007 seconds. (ie. 000, 003, 007, 010, 013, 017, 020, ..., 997)
This is not the case with the value 2015-07-27 23:59:59.999 which is within this range: 2015-07-27 23:59:59.997 and 2015-07-28 0:00:00.000.
This range correspond to the closest preceding and following options, both ending with either .000, .003 or .007.
Rounding up or down?
Because it is closer to 2015-07-28 0:00:00.000 (+1 versus -2) than 2015-07-27 23:59:59.997, the string is rounded up and becomes this Datetime value: 2015-07-28 0:00:00.000.
With an upper limit like 2015-07-27 23:59:59.998 (or .995, .996, .997, .998), it would have been round down to 2015-07-27 23:59:59.997 and you query would have worked as expected.
However it would not have been a solution but just a lucky value.
Datetime2 or Time types
Datetime2 and Time time ranges are 00:00:00.0000000 through 23:59:59.9999999 with an accuracy of 100ns (the last digit when used with a 7 digit precision).
However a Datetime(3) range is not similar to Datetime range:
0:0:00.000 to 23:59:59.9970:0:00.000000000 to 23:59:59.999Solution
In the end it is safer to look for dates below the next day than dates below or equal to what you think it the last fragment of time of the day. This is mainly because you know that the next day always starts at 0:00:00.000 but different data types may not have the same time at the end of the day:
Datetime `0:0:00.000` to `23:59:59.997`
Datetime2 `0:0:00.000000000` to `23:59:59.999-999-900`
Time2 `0:0:00.000000000` to `23:59:59.999-999-900`
< 2015-07-28 0:00:00.000 will give you an accurate results and is the best option<= 2015-07-27 23:59:59.xxx may return unexpected values when it is not rounded up to what you think it should be.We could think that changing [posted_date] to Datetime2 and its higher precision could fix this problem but it won't help because the string is still converted to Datetime.
However, if a cast is added cast(2015-07-27 23:59:59.999' as datetime2), this works fine
Cast and Convert
Cast can convert a value with up to 3 digits to Datetime or with up to 9 digits to Datetime2 or Time and round it to the correct precision.
It is to be noted that Cast of Datetime2 and Time2 may give different results:
select cast('20150101 23:59:59.999999999' as datetime2(7)) is round up 2015-05-03 00:00:00.0000000 (for value greater than 999999949)select cast('23:59:59.999999999' as time(7)) => 23:59:59.9999999It sort of fix the problem datetime is having with the 0, 3 and 7 increments although it is still always better to look for dates before the 1st nano second of the next day (always 0:00:00.000).
Source MSDN: datetime (Transact-SQL)
It is rounding
select cast('2015-07-27 23:59:59.999' as datetime)
returns 2015-07-28 00:00:00.000
.998, .997, .996, .995 all cast / round to .997
Should use
select *
from A
where posted_date >= '2015-07-27 00:00:00.000'
and posted_date < '2015-07-28 00:00:00.000'
or
where cast(posted_date as date) = '2015-07-27'
See accuracy in this link
Always reported as .000, .003, .007
