Selected datetime value doesn't match the original value on SQL Server 2016

[hito4t]

Driver version or jar name

mssql-jdbc-6.1.6.jre7.jar

SQL Server version

SQL Server 2016

Client operating system

Windows 7 Enterprise (64bit)

Java/JVM version

java version "1.7.0_79"

Table schema

CREATE TABLE DATETIMETEST (
Id int,
UpdateDate datetime,
PRIMARY KEY (Id)
);

INSERT INTO DATETIMETEST VALUES(1, '2018-04-01 12:00:00.000');
INSERT INTO DATETIMETEST VALUES(2, '2018-04-01 12:00:01.003');

Problem description

On SQL Server 2016, selected datetime value doesn't match the original value.
It must be caused by the following change.
https://support.microsoft.com/en-us/help/4010261/sql-server-and-azure-sql-database-improvements-in-handling-some-data-t

But the problem doesn't occur in .NET.
Is it a problem of the JDBC driver?

Expected behavior and actual behavior

Expected: it matches.
Actual: it doesn't match.

Repro code

Connection conn = ...;

for (int i = 1; i <= 2; i++) {
    PreparedStatement statement1 = conn.prepareStatement("SELECT UpdateDate FROM DATETIMETEST WHERE ID=?");
    statement1.setInt(1, i);
    ResultSet resultSet1 = statement1.executeQuery();
    resultSet1.next();
    Timestamp date1 = resultSet1.getTimestamp(1);
    resultSet1.close();
    statement1.close();

    PreparedStatement statement2 = conn.prepareStatement("SELECT COUNT(*) FROM DATETIMETEST WHERE ID=? AND UpdateDate=?");
    statement2.setInt(1, i);
    statement2.setTimestamp(2, date1);
    ResultSet resultSet2 = statement2.executeQuery();
    resultSet2.next();
    // When Id=1, count=1
    // When Id=2, count=1 (SQL Server 2012) / count=0 (SQL Server 2016)
    System.out.println("Id=" + i + ", UpdateDate=" + date1 + ", count=" + resultSet2.getInt(1));
    resultSet2.close();
    statement2.close();
}

[David-Engel]

You are correct that the issue is caused by the server-side change you noted. The reason the change breaks things is because the Timestamp data type in JDBC maps to the datetime2 data type in SQL Server. So in the first query, when i = 2, the database returns 2018-04-01 12:00:01.003 which is stored into date1 which becomes the Timestamp 2018-04-01 12:00:01.003000. When you send that Timestamp as a parameter in query 2, the 2016 database sees a comparison between the datetime2 parameter value of 2018-04-01 12:00:01.003000 and the database datetime value of 2018-04-01 12:00:01.003333 which it equates to not equal. (Due to the data handling accuracy "improvements" in 2016, datetime values are made more "accurate" when converting to datetime2 since datetime is actually granular to 1/300 of a second.)

You can see this in practice on the server side by simply running:
select 'equal' where cast('2018-04-01 12:00:01.003' as datetime) = cast ('2018-04-01 12:00:01.003' as datetime2)
It returns 'equal' on SQL Server 2014 and below but no rows on SQL Server 2016 and up.
This query shows how the database converts the datetime to a "more accurate" datetime2 in SQL Server 2016 and up:
select cast(cast('2018-04-01 12:00:01.003' as datetime) as datetime2), cast ('2018-04-01 12:00:01.003' as datetime2)

The reason this is not an issue in .NET is .NET has native DateTime and DateTime2 data types whereas JDBC only has Timestamp.

The most reasonable workarounds are to run the database in SQL Server 2014 compatibility mode or convert all datetime columns to datetime2(3).

[ulvii]

Thank you for detailed explanation, @David-Engel!

The issue seems to be related to #443, we will try to add a fix in one of the upcoming releases.

[hito4t]

@David-Engel @ulvii
Thank you!
I look forward to the release.

[idaamit]

I have encountered this issue as well. What is the status of this issue ?

[David-Engel]

Unfortunately, this was a server-side change that does not seem to be fixable on the driver-side and is not going to change back on the server-side. We've had lengthy discussions with the server team and they had valid reasons for making the change and will not be going back. Ultimately, the best option is to move away from the old datetime data types.

[peterbae]

I've added a wiki page that explains this behavior. Since we won't be fixing this issue on the driver's end, we'll close this issue.

[AlBundy33]

why not let the users decide (e.g. with a datasource-property/jdbc-connection-string-parameter) how to handle timestamps (datetime vs datetime2)
-> assuming that one database uses just one of the types?

in my opinion this change is much more a bug than a feature if we can't query timestamps that we've selected beforehand from the same database (see #1843, #1520, #443, ...)

[Jeffery-Wasty]

Hi @AlBundy33,

We're sticking with the current solution for the time being, but if you're able to provide a PR with the proposed changes, we can look into integrating those changes into the driver.

[AlBundy33]

but there is already #1687 from @dswitzer trying to resolve the problem 🤔

[dswitzer]

We've been using the fix in #1687 with great success. I understand why the original decision was made, but the adding the JDBC property has solved the problem for us. And if the legacy database is updated, then we can remove the property. Best of both worlds and adding support for the JDBC parameter is very straightforward.

[Jeffery-Wasty]

Thanks for pointing out this PR. We can take another look at #1687 in an upcoming sprint and integrate that if it indeed resolves the problem.