T-SQL Programming: DateTime DataType Part2

This document describes the storage format of DateTime and how to use DateTime. This document describes the rounding of the DateTime type.

2. rounding issues)

The conversion between DateTime and String is always accurate. For example, if "20060923 03: 23: 47: 001" is converted to the DateTime type, this option is converted to the time closest to 3% seconds. It will be converted to "20060923 03:23:47. 000 ". Why is the Conversion Result like this? It is because the data is always converted to the nearest time from 3/1000 seconds as the computing unit.Can we understand the conversion in this way? In fact, the result of this conversion can be. 000 and. 003. Based on the principle of the latest 3/1000 seconds ,. 001. 000 is 1 millisecond. 001. 003 is 2 ms, so the result is. 000). If the above string is converted to the Smalldatetime type, the result will be "20060923 03:24:00". If it is converted to SmallDateTime, the result will always be converted to the time closest to the calculation unit.It can be understood that when 03:23:47 is converted to SmallDateTime, it can be converted to 03:23:00 and 03:24:00. The two results show that the conversion character is only 13 seconds away from 03:24:00, so it is converted to 03:24:00 .)What is the conversion result if the Conversion characters are "starting at 03:23:30" and "03:23:29? The result is "03:24:00" and "03:23:00 ".

Similarly, when the String type is converted to DateTime, the results are inaccurate. The String type value is converted to the DateTime type value that is closest to the String type value. Instead of converting the Decimal type value to the INT type, simply remove the subsequent Decimal part, for example, the 10.99 conversion result is 10. The millisecond part of DateTim data always follows the following format: [0-9] [0-9] [037]. For example, if the value in milliseconds is 994, the final result will be 997. 999 will be converted to 000 in the next second when it is converted to the DateTime type. Therefore, this is not a good filter statement for the following statements:

Where Dt between '2014 00:00:00. 000' and '2014 23:59:59. 123'. Why? According to the above analysis, '2017 23:59:59. 123' will be converted to '2017 00:00:00. 000 '. Therefore, based on the preceding conditions, the final result will contain data of '2017 00:00:00. 000. To improve the content above, we can change the value of the upper boundary to '2017 23:59:59. 123 '. In this way, the data of '2017 00:00:00. 000' will not be contained. The following describes how to use better conditions:

Where Dt> = '2014 00:00:00. 000' and Dt <'2014 00:00:00. 000'. This method can be used normally regardless of the DateTime or SmallDateTime type.

When using the preceding Dt> = '2014 00:00:00. 000' and Dt <'2014 00:00:00. 000', some programmers prefer to remove the time part and write it as follows:

Dt> = '000000' and Dt <'000000' in this form, SQL Server converts the character form to the midnight time of the specified date. That is, '123' is converted to 20060211 00:00:00. 000.

The above filter condition is Search ArgumentSARG), which means that the query optimizer will consider using the index query operation to improve performance.However, some people will write the following form, use the SQL Server function to retrieve the date part, and then compare it as follows:

Where ConvertVarchar (8), dt, 112) = '000000' is not in the SARG format, and the database does not use index queries. Therefore, this method should be avoided.

1. Instance application of T-SQL with Keyword
2. Easily debug T-SQL statements in SQL Server 2008
3. T-SQL statement create SQL Server Index
4. Microsoft SQL Server 2008 technical Insider: T-SQL Query
5. Introduction to T-SQL programming classic (covering SQL Server 2008 & 2005)