【转】SQL SERVER DateTime类型的精度

先看下边的SQL 语句

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

查询语句查询的是’2010-01-02 00:00:00.000’到’2010-01-04 00:00:00.000’的时间，所以查询结果为2,3,4,5,6。

再执行如下的SQL 语句：

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

查询结果为：

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

在执行结果中，ID为2的日期变成2010-01-02 00:00:00.000.这是为什么呢？

因为，在SQL SERVER中DATETIME表示的时间为00:00:00到23:59:59.997，它的时间精度为1/300秒，在使用时会舍入到舍入到 .000、.003 或 .007 秒三个增量。如下表是DATETIME秒的小数部分精度的舍入。

根据这一规则，在上边的SQL语句中，实际插入表中的数据为：

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

在查询某一天当中的数据时，如果查询条件为BETWEEN  ‘2010-01-02T00:00:00.000’

AND ‘2010-01-02T23:59:59.999′,实际查询的是’2010-01-02T00:00:00.000’

到’2010-01-03T00:00:00.000’之间的数据。

例子如，将上边的SQ语句的查询条件更改为：

SELECT @DATE1 = ‘2010-01-02T00:00:00.000’

SELECT @DATE2 = ‘2010-01-02T23:59:59.999’

其执行结果：

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

所以，在查询某一天的数据时，查询条件应为：BETWEEN  ‘2010-01-02T00:00:00.000’

AND ‘2010-01-02T23:59:59.998’。

更改查询条件，重新执行查询：

SELECT @DATE1 = ‘2010-01-02T00:00:00.000’

SELECT @DATE2 = ‘2010-01-02T23:59:59.998’

其执行结果为：

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

在SQL SERVER 2008中新增了Datetime2类型，可以提供更高的时间精度，且可以自定义时间精度，以后可以使用这种数据类型。