Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Wrong Results When Selecting SQLServer2016 JOIN DATETIME2(3) with DATETIME

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

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

although values are equal, I get only part of the rows.

But If I remove the PK or change compatibility level to COMPATIBILITY_LEVEL = 120

I get all rows as expected

Is it a bug ?

Is there a better way to do this join.

Note: I join to the same table only for the simplicity of the example in real life I join between 2 different tables.

54 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Ida Amit shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base