JDBC driver is mapping date to java.util.Timestamp which is getting converted into datetime2 in sqlserver causing issue in data comparison

I am using spring batch framework in my product. Which is reading some data from mssqlserver database, processing the data and updating the processed data in the same table using primary key in where clause. mssql-jdbc driver is coming into picture for the communication with databases.

This is the schema of my table CREATE TABLE sampletable( col1 datetime NOT NULL primary key, col2 varchar(50), col3 varchar(50) )

This is the data in my table

col1                    |col2   |col3
2024-04-28 20:18:43.703 name    lastname

And when spring framework is executing update query then at database side in sqlserver profiler below is the query which is getting executed. exec sp_executesql N'update sampletable set col2=@P0 where col1=@P1 ',N'@P0 nvarchar(4000),@P1 datetime2',N'xxxxxxx','2024-04-28 20:18:43.7030000'

And it does not update any row because '2024-04-28 20:18:43.7030000' does not matches to '2024-04-28 20:18:43.703' after implicit conversion.

We are aware about the "Starting from SQL Server 2016, the conversion / comparison process of datetime to datetime2 (and vice versa) has been modfied."

We have also tried casting datetime value to datetime2 and passed in the query but that also didn't help. This is the query that we created after casting exec sp_executesql N'update sampletable set col2=@P0 where col1=@P1 ',N'@P0 nvarchar(4000),@P1 datetime2',N'xxxxxxx','2024-04-28 20:18:43.7033333'

Is there any way by which we can stop this conversion of datetime to datetime2 while executing using jdbc in sqlserver.

This works in 12.2.0:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import org.junit.Test;

public class JDBCTest
{
    @Test
    public void testJdbc_default() throws Exception
    {
        Connection c = DriverManager
                .getConnection(
                        "jdbc:sqlserver://localhost;databaseName=;integratedSecurity=true;"
                            + "encrypt=false;lastUpdateCount=false;");
        PreparedStatement ps = c.prepareStatement("select convert(varchar(30), ?, 121)");
        ps.setTimestamp(1, Timestamp.valueOf("2024-04-28 20:18:43.703"));
        ps.execute();
        ResultSet rs = ps.getResultSet();
        rs.next();
        System.out.println(rs.getString(1)); //2024-04-28 20:18:43.7030000
    }

    @Test
    public void testJdbc_datetime() throws Exception
    {
        Connection c = DriverManager
                .getConnection(
                        "jdbc:sqlserver://localhost;databaseName=;integratedSecurity=true;"
                            + "encrypt=false;lastUpdateCount=false;datetimeParameterType=datetime");
        PreparedStatement ps = c.prepareStatement("select convert(varchar(30), ?, 121)");
        ps.setTimestamp(1, Timestamp.valueOf("2024-04-28 20:18:43.703"));
        ps.execute();
        ResultSet rs = ps.getResultSet();
        rs.next();
        System.out.println(rs.getString(1)); //2024-04-28 20:18:43.703
    }

    @Test
    public void testJdbc_datetime2() throws Exception
    {
        Connection c = DriverManager
                .getConnection(
                        "jdbc:sqlserver://localhost;databaseName=;integratedSecurity=true;"
                            + "encrypt=false;lastUpdateCount=false;datetimeParameterType=datetime2");
        PreparedStatement ps = c.prepareStatement("select convert(varchar(30), ?, 121)");
        ps.setTimestamp(1, Timestamp.valueOf("2024-04-28 20:18:43.703"));
        ps.execute();
        ResultSet rs = ps.getResultSet();
        rs.next();
        System.out.println(rs.getString(1)); //2024-04-28 20:18:43.703000
    }
}

Pom:

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>12.2.0.jre8</version>
</dependency>

Maybe something else is going on for you?