Bug report

It seems the Debezium SQL Server connector produces an incorrect representation of some datetime values in a topic.

What Debezium connector do you use and what version?

Debezium SQL Server connector 2.6.1

What is the connector configuration?

The source connector is as follows for simplicity:

{ 
"connector.class": "io.debezium.connector.sqlserver.SqlServerConnector", 
"database.encrypt": "false", 
"database.hostname": "XXX.XXX.XX.XXX", 
"database.names": "test", 
"database.password": "mypass", 
"database.port": "1433", 
"database.server.name": "test", 
"database.user": "myuser", 
"heartbeat.interval.ms": "5000", 
"name": "mssql-connector-test-date-tmp", 
"request.timeout.ms": "10000", 
"schema.history.internal.kafka.bootstrap.servers": "XXX.XXX.XX.XXX:XXXX", "schema.history.internal.kafka.recovery.poll.interval.ms": "10000", "schema.history.internal.kafka.topic": "dbhistory.test", 
"table.include.list": "dbo.test_date_tmp", 
"tasks.max": "1", 
"time.precision.mode": "connect", 
"topic.prefix": "my" 
}

What is the captured database version and mode of depoyment?

Enterprise Edition Microsoft SQL Server 2019 (RTM-CU23) (KB5030333) 

What behaviour do you expect?

I expect to get value of 386467200000 in a topic for datetime 1982-04-01 00:00:00.000. As far as I know, the Debezium SQL Server connector converts a datetime value to the number of milliseconds since the epoch, independent of the local system's UTC setting and this is done using UTC as the reference time zone.

What behaviour do you see?

I got the value 386470800000 milliseconds, which corresponds to a different datetime: 1982-04-01 01:00:00.000, whereas other datetime values are represented in the topic as expected.

Do you see the same behaviour using the latest relesead Debezium version?

Yes

Do you have the connector logs, ideally from start till finish?

No

How to reproduce the issue using our tutorial deployment?

I have a test table enabled for CDC on MS SQL Server 2019:

CREATE TABLE test_date_tmp (id INT IDENTITY(1,1) PRIMARY KEY, test_date DateTime)

After inserting the following datetime value in the yyyy-mm-dd hh:mi:ss.nnn format:

INSERT INTO test_date_tmp (test_date) VALUES ('1982-04-01 00:00:00.000')

I received the following representation in the topic, which seems to have an incorrect value for "test_date":

... "payload":{ "before":NULL "after":{ "id":1 "test_date":386470800000 } ...