The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity with SQL Server through the standard JDBC application program interfaces (APIs).
MIT License
1.05k
stars
424
forks
source link
Bulk copy: Conversion failed on OffsetDateTime to DATETIME2 #2518
Microsoft SQL Server 2022 (RTM-CU13) (KB5036432) - 16.0.4125.3 (X64)
Azure SQL
AWS RDS with latest available engine (2022)
Client Operating System
MacOS
JAVA/JVM version
Azul Zulu version 11.0.22
Table schema
CREATE TABLE dt_bug(dt DATETIME2);
Problem description
When inserting an OffsetDateTime with both seconds and nanoseconds set to zero using Bulk copy, the insert fails with java.sql.BatchUpdateException: Conversion failed when converting date and/or time from character string.
// testing against local docker container https://hub.docker.com/r/microsoft/mssql-server
final SQLServerDataSource ds = new SQLServerDataSource();
ds.setURL("jdbc:sqlserver://;serverName=localhost;databaseName=db-name");
ds.setEncrypt("false");
ds.setUser("username");
ds.setPassword("password");
ds.setUseBulkCopyForBatchInsert(true);
final Connection con = ds.getConnection();
final OffsetDateTime odt = OffsetDateTime.now().withSecond(0).withNano(0);
final PreparedStatement ps = con.prepareStatement("insert into dt_bug(dt) values(?)");
ps.setObject(1, odt);
ps.addBatch();
ps.executeBatch();
However, this prepared statement succeeds with Bulk copy disabled: ds.setUseBulkCopyForBatchInsert(false);
Expected behavior
Prepared statement succeeds when using Bulk copy.
Actual behavior
Prepared statement fails when using Bulk copy.
Error message/stack trace
Conversion failed when converting date and/or time from character string.
java.sql.BatchUpdateException: Conversion failed when converting date and/or time from character string.
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2231)
I'm looking into this. Curiously, this looks like a problem we already resolved with a previous PR, yet you are using the latest version. I'll let you know what we find out.
Driver version
SQL Server version
Client Operating System
MacOS
JAVA/JVM version
Table schema
CREATE TABLE dt_bug(dt DATETIME2);
Problem description
When inserting an OffsetDateTime with both seconds and nanoseconds set to zero using Bulk copy, the insert fails with
java.sql.BatchUpdateException: Conversion failed when converting date and/or time from character string.
However, this prepared statement succeeds with Bulk copy disabled:
ds.setUseBulkCopyForBatchInsert(false);
Expected behavior
Prepared statement succeeds when using Bulk copy.
Actual behavior
Prepared statement fails when using Bulk copy.
Error message/stack trace