Problems with the SQL Server datetime data type

Recently, when checking a cashier report, I found a problem, that is, when the string value of the time date in SQL Server goes to the datetime type, something strange happens.

The query is as follows:

You need to check the cashier data for the 2015-07-01 day. The incoming date is: Start date ' 2015-07-01 00:00:00.000 ', End Date ' 2015-07-01 23:59:59.999 '.

After the query, it is found that the gift amount of this cashier report and other reports of the amount of money not on. After tracking discovery, the gift amount is a table a stored, the date field A is a char type, only the value of the date part is saved.

When the end date ' 2015-07-01 23:59:59.999 ' value of our descendants is converted to a datetime type, the value becomes ' 2015-07-02 00:00:00.000 ', so that when querying a table, the 2015-07-02
Days of the gift amount is also queried out, so that the amount of money on more data, nature and some statements are not on the.

Well, now there's a problem: 1 seconds equals 1000 milliseconds, so why is the ' 2015-07-01 23:59:59.999 ' value converted to a datetime type and the value becomes ' 2015-07-02 00:00:00.000 '?

Query the Help documentation for SQL Server with the following description in the documentation:

datetime: date and time data, from January 1, 1753 to December 31, 9999, with an accuracy of 1% seconds or 3.33 milliseconds. Values are rounded to. 000,. 003, or. 007 ms increments.

Later, in Itzikben-gan's book, Microsoft SQL Server 2005 Technology Insider: T-SQL query, there is also a description of the DateTime type: The millisecond partial mode of the DateTime type is [0-9][0-9][037].
That is, the last one in the millisecond section, there are only three possible values: 0, 3, 7, and a 1/3 approximate. A value of 9 o'clock rounds to the next second.

This is why the ' 2015-07-01 23:59:59.999 ' value is converted to a datetime type, and the value becomes ' 2015-07-02 00:00:00.000 '.

Problems with the SQL Server datetime data type