Bulk Copy API does not perform Timezone conversion for DATETIME2(3) with PreparedStatement.setTimestamp

[PriyadharshiniP]

Driver version

mssql-jdbc version 12.4.1.jre11

SQL Server version

Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: )

Client Operating System

Windows

JAVA/JVM version

Java 17

Problem description

DATETIME2(3) type is supported by the Bulk Copy API. DATETIME2 is not there in the unsupported datatypes list as per: https://learn.microsoft.com/en-us/sql/connect/jdbc/using-bulk-copy-with-the-jdbc-driver?view=sql-server-ver16

Nevertheless, it was discovered that there appears to be NO timezone conversion with Bulk copy API for DATETIME2(3) column type.
The following is specified in the PreparedStatement.setTimestamp contract: "Sets the designated parameter to the given {@code java.sql.Timestamp} value,using the given {@code Calendar} object. The driver usesthe {@code Calendar} object to construct an SQL {@code TIMESTAMP} value, which the driver then sends to the database. With a {@code Calendar} object, the driver can calculate the timestamptaking into account a custom timezone. If no{@code Calendar} object is specified, the driver uses the default timezone, which is that of the virtual machine running the application."
Bulk copy API seems to ignore this calendar settings and inserts the timestamp value in the default timezone.
Please see the following stand alone Java code that inserts the first row WITHOUT bulk API and the second row WITH bulk API.

package com.test;

import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestBulkAPI {
	public static void main(String[] args) throws Exception {
		String tableNameBulkCopyAPI = "testTable";
		List<Timestamp> loT = new ArrayList<>();
		Calendar gmtCal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date currentDate = new Date();
		String currentDateString = simpleDateFormat.format(currentDate);
		long rangeEnd = Timestamp.valueOf(currentDateString).getTime();
		Timestamp timestamp = new Timestamp(rangeEnd);
		loT.add(timestamp);

		String connectionUrl = "jdbc:sqlserver://<serverName>:1433;encrypt=false;databaseName=<dbName>;user=<userName>;password=<pwd>;statementPoolingCacheSize=10;disableStatementPooling=false;enablePrepareOnFirstPreparedStatementCall=true;";
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

		// SQL batch insert WITHOUT BULK API
		try (Connection con = DriverManager.getConnection(connectionUrl);
				Statement stmt = con.createStatement();
				PreparedStatement pstmt = con.prepareStatement(
						"insert into " + tableNameBulkCopyAPI+ " values (?, ?, ?)")) {

			String dropSql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tableNameBulkCopyAPI + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [" + tableNameBulkCopyAPI + "]";
	        stmt.execute(dropSql);

			String createSql = "create table " + tableNameBulkCopyAPI + " (c1 int, c2 varchar(50), c3 datetime2(3) )";
	        stmt.execute(createSql);

			pstmt.setInt(1, 1);
			pstmt.setString(2, "test"+currentDateString);
			// Time zone conversion to GMT is fine
			pstmt.setTimestamp(3, loT.get(0), gmtCal);
			pstmt.addBatch();
			pstmt.executeBatch();

		}

		// SQL batch insert WITH  BULK COPY API
		try (Connection con = DriverManager.getConnection(connectionUrl + ";useBulkCopyForBatchInsert=true");
				Statement stmt = con.createStatement();
				PreparedStatement pstmt = con.prepareStatement(
						"insert into " + tableNameBulkCopyAPI+ " values (?, ?, ?)")) {

			pstmt.setInt(1, 2);
			pstmt.setString(2, "test"+currentDateString);
			// Time zone conversion to GMT DOES NOT HAPPEN. It inserts using the given timestamp value WITHOUT converting to GMT.
			pstmt.setTimestamp(3, loT.get(0), gmtCal);
			pstmt.addBatch();
			pstmt.executeBatch();

		}

	}
}

Actual behavior

The first row is inserted correctly without Bulk copy API.
As you can see, there is a problem with the second row that was put using the Bulk Copy API because the c3 value is utilizing the default time zone rather than GMT.

Expected behavior

The date entered in the third field using the stand alone program mentioned above should be changed to GMT timezone rather than the local timezone date value when using bulk copy API.
The c3 value for second row should also be "2023-12-04 00:43:29.000"

Error message/stack trace

No error happens.

[Jeffery-Wasty]

Hi @PriyadharshiniP,

We'll take a look at this, thank you.

[PriyadharshiniP]

Also noticed that the com.microsoft.sqlserver.jdbc.bulkCopy.BulkCopyResultSetCursorTest unit test case will FAIL when using the expectedTimestamps value as new Timestamp(1696127400000L). The expectedTimestampStrings for this is 2023-10-01 02:30:00.0000000 in GMT inserted correctly in the source table. But a value of 2023-10-01 03:30:00.0000000 is inserted by bulk copy in the destination table. Please note this specific GMT datetime does not exist in my local AU time zone due to day light savings.

[Jeffery-Wasty]

Hi @PriyadharshiniP,

We're still in the process of exploring the issue. We have been able to reproduce so far though. A question we have: is it possible to use DateTimeOffset in your situation? DateTimeOffset inherently includes timezone information, and so would help avoid the situation you describe.

[PriyadharshiniP]

Hi @Jeffery-Wasty
Thanks for the update.
Unfortunately, my test indicates that data type datetimeoffset is not supported in bulk copy, and this is also stated in the manual (https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver16).

[Jeffery-Wasty]

Hi @PriyadharshiniP,

Thank you for the update. We're currently working on fixing the above use case for DateTime2 with BulkCopy.