6

I'm getting unexpected results when joining on a DATETIME2(3) and a DATETIME column with PK, in SQL Server 2016.

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 the values are equal, I only get some of the rows.

But if I remove the PK or change compatibility level to COMPATIBILITY_LEVEL = 120, then 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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ida Amit
  • 1,411
  • 2
  • 13
  • 27
  • Possible duplicate of [Why does conversion from DATETIME to DATETIME2 appear to change value?](https://stackoverflow.com/questions/48327065/why-does-conversion-from-datetime-to-datetime2-appear-to-change-value) – Dan Guzman May 21 '19 at 12:11
  • @DanGuzman This is not a duplication since the post compares between DATETIME(7) and DATETIME. In my post , I understand that implicit conversion doesn't work. so, I made explicit conversion but the join fails because of existance of PK – Ida Amit May 21 '19 at 13:24
  • I have opened a bug at https://feedback.azure.com/forums/908035-sql-server/suggestions/37717732-wrong-results-when-selecting-sqlserver2016-join-da – Ida Amit May 26 '19 at 07:37
  • Thanks for taking the time for creating the bug report, Upvoted. – Dan Guzman May 26 '19 at 12:20

1 Answers1

0

Actually, I have come across this as well. It is definitely a valid question to Microsoft SQL Server Team. Thank you for taking the time to raising this bug.

But as an alternative solution, you should rather try to convert the 'richer' datatype (DATETIME2) to the 'poorer' datatype (DATETIME) as backward compatible way. And then it would give you the results you are looking for:

SELECT      
    a.DATETIME,
    a.DATETIME2_3
FROM
    DATETIME_TEST a
INNER JOIN 
    DATETIME_TEST b ON a.[DATETIME] = CONVERT(DATETIME, b.[DATETIME2_3])
san
  • 1,415
  • 8
  • 13
  • @Ida Amit, could you please let me now if the above query did the job for you? – san Jun 16 '19 at 20:51
  • You might get wrong results, for the following data : INSERT INTO DATETIME_TEST ([DATETIME], [DATETIME2_3]) VALUES ('20020202 02:02:02.003', '20020202 02:02:02.002'); Since 002 milisec is rounded to 003. – Ida Amit Jun 17 '19 at 13:55
  • hmmm.. but isn't it the case anyways with DATETIME irrespective of whether you are trying to convert or not? For eg. execute this: – san Jun 17 '19 at 19:13
  • DROP TABLE IF EXISTS DATETIME_TEST; 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.003', '20020202 02:02:02.003'), ('20020206 02:02:02.056', '20020206 02:02:02.056'); SELECT * FROM DATETIME_TEST; SELECT * FROM DATETIME_TEST WHERE CONVERT(DATETIME2(3), [DATETIME]) = [DATETIME2_3]; – san Jun 17 '19 at 19:13
  • When I examine SQL statement results I compare it to the existing rows in the table, not to what I have tried to insert. So, WHERE CONVERT(DATETIME2(3), [DATETIME]) = [DATETIME2_3] brings you the right results. – Ida Amit Jun 18 '19 at 04:17