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 ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
Benefits of Using
datetimesupporting only 3 decimal places .. and hence you see the rounding issue since by default
datetimerounds the nearest
.003 secondswith increments of
datetime2is much more precise than
datetime2gives you control of
TIMEas opposed to
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
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.
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
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.
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.
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
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:
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
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:
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.000will give you an accurate results and is the best option
<= 2015-07-27 23:59:59.xxxmay 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.9999999
It 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
select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date < '2015-07-28 00:00:00.000'
where cast(posted_date as date) = '2015-07-27'
See accuracy in this link
Always reported as .000, .003, .007