SQLServer2016使用DATETIME加入DATETIME2(3) [英] SQLServer2016 JOIN DATETIME2(3) with DATETIME
问题描述
在SQLServer 2016中,在DATETIME2(3)列与DATETIME列之间加入PK时,我得到了意外的结果。
I'm getting unexpected results when joining between DATETIME2(3) column to DATETIME column with PK , in SQLServer 2016.
详细信息如下:
The details are :
我有下表:
I have the following table:
CREATE TABLE DATETIME_TEST (
[DATETIME] DATETIME NOT NULL,
[DATETIME2_3] DATETIME2(3));
ALTER TABLE DATETIME_TEST ADD CONSTRAINT PK_DATETIME_TEST PRIMARY KEY ([DATETIME]);
INSERT INTO DATETIME_TEST
( [DATETIME],[DATETIME2_3])
VALUES
('20020202 02:02:02.000', '20020202 02:02:02.000' ),
('20020202 02:02:02.003', '20020202 02:02:02.003' ),
('20020202 02:02:02.007', '20020202 02:02:02.007' ),
('2019-04-28 07:23:29.447', '2019-04-28 07:23:29.447' )
;
SELECT * FROM DATETIME_TEST WHERE CONVERT(DATETIME2(3), [DATETIME]) = [DATETIME2_3]
The results :
DATETIME DATETIME2_3
2002-02-02 02:02:02.000 2002-02-02 02:02:02.000
2002- 02-02 02:02:02.003 2002-02-02 02:02:02.003
2002-02-02 02:02:02.007 2002-02-02 02:02:02.007
2019-04- 28 07:23:29.447 2019-04-28 07:23:29.447
DATETIME DATETIME2_3 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 2019-04-28 07:23:29.447 2019-04-28 07:23:29.447
如上所示, 值相等。
As you can see above, the values are equal .
SELECT
a.DATETIME,
a.DATETIME2_3
FROM DATETIME_TEST a
INNER JOIN DATETIME_TEST b ON CONVERT(DATETIME2(3),a. [DATETIME]) = b.[DATETIME2_3]
The results :
DATETIME2_3 DATETIME
2002-02-02 02:02:02.000 2002-02-02 02:02:02.000
DATETIME2_3 DATETIME 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000
虽然价值相等,但我只获得部分行。
although values are equal, I get only part of the rows.
但如果我删除PK或更改兼容性级别为 COMPATIBILITY_LEVEL = 120
But If I remove the PK or change compatibility level to COMPATIBILITY_LEVEL = 120
我按预期获得所有行
这是一个错误吗?
Is it a bug ?
有没有更好的方法来加入。
Is there a better way to do this join.
注意:我加入同一个表只是为了简化现实生活中的例子我在2个不同的表之间加入。
Note: I join to the same table only for the simplicity of the example in real life I join between 2 different tables.
推荐答案
您可能遇到
此问题。不幸的是,MS通过对Connect的更改搞砸了所有内容,但该线程至少涵盖了基础知识。
You might be running into this issue with a 2016 behavior change. It is unfortunate the MS screwed everything with the changes to Connect but that thread at least covers the basics.
这篇关于SQLServer2016使用DATETIME加入DATETIME2(3)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!