select gives wrong result in case of comparing datetime with datetime2

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.

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?

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.

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.

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)