The precision of the "Go" SQL SERVER datetime type

First look at the SQL statement below

CREATE TABLE #DateTest (

Id INT,

Sampledate DATETIME

)

INSERT into #DateTest VALUES (1, ' 1 Jan 2010 10:30 ')

INSERT into #DateTest VALUES (2, ' 2 Jan 2010 23:59 ')

INSERT into #DateTest VALUES (3, ' 3 Jan 2010 12:34 ')

INSERT into #DateTest VALUES (4, ' 3 Jan 2010 12:56 ')

INSERT into #DateTest VALUES (5, ' 4 Jan 2010 ')

INSERT into #DateTest VALUES (6, ' 4 Jan 2010 00:00 ')

INSERT into #DateTest VALUES (7, ' 4 Jan 2010 13:31 ')

INSERT into #DateTest VALUES (8, ' 4 Jan 2010 00:10 ')

INSERT into #DateTest VALUES (9, ' 5 Jan 2010 12:01 ')

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

SELECT @DATE1 = ' 2 Jan 2010 '

SELECT @DATE2 = ' 4 Jan 2010 '

SELECT Id

From #DateTest

WHERE sampledate between @DATE1 and @DATE2

DROP TABLE #DateTest

Query statement query is ' 2010-01-02 00:00:00.000 ' to ' 2010-01-04 00:00:00.000 ' time, so the query result is 2,3,4,5,6.

Then execute the following SQL statement:

CREATE TABLE #DateTest (

Id INT,

Sampledate DATETIME

)

INSERT into #DateTest VALUES (1, ' 2010-01-01t10:30:21.000 ')

INSERT into #DateTest VALUES (2, ' 2010-01-01t23:59:59.999 ')

INSERT into #DateTest VALUES (3, ' 2010-01-02t12:56:00.000 ')

INSERT into #DateTest VALUES (4, ' 2010-01-02t23:59:59.998 ')

INSERT into #DateTest VALUES (5, ' 2010-01-03t00:00:00.000 ')

INSERT into #DateTest VALUES (6, ' 2010-01-03t13:31:00.000 ')

INSERT into #DateTest VALUES (7, ' 2010-01-04t00:00:00.001 ')

INSERT into #DateTest VALUES (8, ' 2010-01-04t00:00:00.002 ')

INSERT into #DateTest VALUES (9, ' 2010-01-05t12:01:00.002 ')

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

SELECT @DATE1 = ' 2 Jan 2010 '

SELECT @DATE2 = ' 4 Jan 2010 '

SELECT *

From #DateTest

WHERE sampledate between @DATE1 and @DATE2

DROP TABLE #DateTest

The query results are:

Id sampledate

----------- -----------------------

2 2010-01-02 00:00:00.000

3 2010-01-02 12:56:00.000

4 2010-01-02 23:59:59.997

5 2010-01-03 00:00:00.000

6 2010-01-03 13:31:00.000

7 2010-01-04 00:00:00.000

In the execution result, the date with ID 2 becomes the 2010-01-02 00:00:00.000. Why is that?

Because, in SQL Server, DateTime represents a time of 00:00:00 to 23:59:59.997, its time precision is 1/300 seconds, and when used, it is rounded to three increments of round to. 000,. 003, or. 007 seconds. The following table is a rounding of the decimal part precision for a datetime second.

User-Specified value	Values stored by the system
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995 01/01/98 23:59:59.996 01/01/98 23:59:59.997 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992 01/01/98 23:59:59.993 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990

According to this rule, in the SQL statement above, the data actually inserted into the table is:

Id sampledate

----------- -----------------------

1 2010-01-01 10:30:21.000

2 2010-01-02 00:00:00.000

3 2010-01-02 12:56:00.000

4 2010-01-02 23:59:59.997

5 2010-01-03 00:00:00.000

6 2010-01-03 13:31:00.000

7 2010-01-04 00:00:00.000

8 2010-01-04 00:00:00.003

9 2010-01-05 12:01:00.003

When querying data for a given day, if the query condition is between ' 2010-01-02t00:00:00.000 '

and ' 2010-01-02t23:59:59.999 ', the actual query is ' 2010-01-02t00:00:00.000 '

To the data between ' 2010-01-03t00:00:00.000 '.

For example, change the query condition of the upper sq statement to:

SELECT @DATE1 = ' 2010-01-02t00:00:00.000 '

SELECT @DATE2 = ' 2010-01-02t23:59:59.999 '

The result of its execution:

Id sampledate

----------- -----------------------

2 2010-01-02 00:00:00.000

3 2010-01-02 12:56:00.000

4 2010-01-02 23:59:59.997

5 2010-01-03 00:00:00.000

Therefore, when querying data for a given day, the query condition should be: between ' 2010-01-02t00:00:00.000 '

and ' 2010-01-02t23:59:59.998 '.

To change the query criteria, re-execute the query:

SELECT @DATE1 = ' 2010-01-02t00:00:00.000 '

SELECT @DATE2 = ' 2010-01-02t23:59:59.998 '

The result of the execution is:

Id sampledate

----------- -----------------------

2 2010-01-02 00:00:00.000

3 2010-01-02 12:56:00.000

4 2010-01-02 23:59:59.997

New Datetime2 types have been added to SQL SERVER 2008 to provide higher time accuracy and to customize the time precision that can be used later.

The precision of the "Go" SQL SERVER datetime type