select gives wrong result in case of comparing datetime with datetime2

Question

Steps to reproduce:
Table TEST with columns LOG_ID decimal(16), LOG_TIME1 datetime, LOG_TIME2 datetime2.

Insert a row with {LOG_ID=1, LOG_TIME1=2021-10-01 11:44:31.857, LOG_TIME2=2021-10-01 11:44:31.857}

Selecting this row again over jdbc or DBeaver delivers exact these data again.

But the following select does not deliver a row
select * from TEST where LOG_TIME1 = LOG_TIME2
[]

The problem is, that LOG_TIME1 seams to be casted to datetime2 internally, but not rounded to milliseconds...

To get the internal representation of the datetime columns, without getting an influence of the jdbc driver, I used the following select:

select LOG_ID, concat('', cast(LOG_TIME1 as datetime2)) CAST_LOG_TIME1, concat('', LOG_TIME2) CAST_LOG_TIME2 from TEST_UPDATE

The result is:
{LOG_ID=1, LOG_TIME1=2021-10-01 11:44:31.8566667, LOG_TIME2=2021-10-01 11:44:31.857}

This shows, that casting "2021-10-01 11:44:31.857" datetime to datetime2 results in: "2021-10-01 11:44:31.8566667".

So datetime is not able to store such an accuracy. It would be best to round the casted result to Milliseconds, to avoid rounding problems...

The problem for me is now using a jdbc driver.
Binding a java.sql.Timestamp seams to create imlicit a datetime2 value in the database. That seams to be a good solution, but...

trying to select a row by a datetime column fails, as the row is not found, due to above reasons.

select * from TEST where LOG_TIME1 = ?

results in no rows.

Microsoft JDBC Driver 9.2 for SQL Server [9.2.0.0]
Database: Microsoft SQL Server [15.00.2000
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: ) (Hypervisor)
]

Answer 1

Irrelevant of the language (SQL, Java, C++, etc) comparing values that are generally irrational such as doubles and date/times using equality is almost always going to be wrong. There is simply too many cases where the system will round (or truncate) values such that you won't get the correct result. The only time that it remotely makes sense to use pure equality is when comparing a truncated/rounded irrational to a whole number. In all other cases you should use relative comparisons instead. The problem you see here is that DATETIME and DATETIME2 are different formats. It would be like comparing a float to a double. It isn't going to work well for all values.

Why do you need to locate a row by a specific time down to the millisecond? This seems like a design problem to me. If you were building a UI that showed all entries for a given timeframe then you'd normally go for something higher like seconds or minutes and then you'd use a bounding expression (< > or BETWEEN in SQL). If you needed an absolute row you'd be using the PK of the table.

DECLARE @logs TABLE (LogId INT, LogTime DATETIME, LogTime2 DATETIME2)

DECLARE @count INT = 50
DECLARE @index INT = 0
DECLARE @now DATETIME = GETDATE()

while @index < @count
BEGIN
   INSERT INTO @logs VALUES (@index + 1, DATEADD(MILLISECOND, 100 * @index, @now), DATEADD(MILLISECOND, 100 * @index, @now))
   SET @index = @index + 1
END

SELECT @now
SELECT * FROM @logs

SELECT * FROM @logs WHERE LogTime BETWEEN DATEADD(second, -1, @now) AND DATEADD(second, 1, @now)
SELECT * FROM @logs WHERE LogTime2 BETWEEN DATEADD(second, -1, @now) AND DATEADD(second, 1, @now)

Answer 2

First of all, you are running the RTM version of SQL 2019. You should download and install Cumulative Update 12 to get many bug fixes, not the least with regards to user-defined scalar functions. However, this particular problem will not be affected.

The datetime data type has a precision of 3.33 ms. So when it says .857, that is really .85677777.

This is a change they made in SQL 2016. If you set the compatibility level of your database to 120, your SELECT will return a row. But since that will through you back on many other things, this is not a viable solution.

But why are you mixing data types? The best data type for date+time in SQL Server is datetime2(3), as there rarely any point with more than millisecond precision. (You would need to get timestamps from an external source for that to be meaningful.)

For change detection, you could consider using rowversion columns. Columns of this data type are automatically updated by SQL Server every time a row is updated.

Answer 3

Hi @Martin Handsteiner ，

Welcome to Microsoft Q&A!

Only based on your table and sample data provided, you could try with below:

select * from test where LOG_TIME1=CAST(LOG_TIME2 AS DATETIME)  

OR

select * from test where CAST(LOG_TIME1 AS DATETIME2(3)) = CAST(LOG_TIME2 AS DATETIME2(3))  

The row of data could be reported in the output from my side.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thanks for the workarounds. But anyway, using any java library (jpa, jvx, ...) will fail, as they do not implement this workaround.

As I already mentioned, this is anyway an issue/ incompatibility with jdbc driver.

Selecting the data (select LOG_TIME1 from TEST) the datetime column is mapped to a
timestamp 2021-10-01 11:44:31.857. So it is rounded to datetime2(3).

Selecting/ updating a row fails, when binding this just selected value.
(update TEST set ... where LOG_TIME1 = ?, setTimestamp(2021-10-01 11:44:31.857))
Even a select getting all dates >= fails:
(select * from TEST where LOG_TIME1 > ?) will not find this row.

The problem is, because the LOG_TIME1 is now casted to datetime2(6): 2021-10-01 11:44:31.8566667.

So either the cast to datetime2(3) from the jdbc driver, selecting the date is a bug, or the cast to datetime2(6) afterwards.

Will there be any chance, that such an issue will be fixed by microsoft?

Answer 5

Hi @Martin Handsteiner ,

It seems that this issue does not appear on SQL Server 2014 and below but appears on SQL Server 2016 and up.

Since your version is SQL Server 2019, you could have a try to lower the database compatibility level to 120 or below.

Or you could try to add a new connection string (e.g. timestampEncoding) which could be used to determine how timestamp should be encoded.

In addition, you could try with below as alternatives:

• Converting the schema to use datetime2.
• Casting values to datetime.

You could refer to more details from below:
Selected datetime value doesn't match the original value on SQL Server 2016
Ability to explicitly specify conversion to datetime or datetime2

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.