Versions:

• Kafka 3.3.1
• Debezium SQL Server connector 2.6.1 latest stable

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"
}

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 got the following representation in the topic:

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

Based on the documentation, the “test_date” value represents the number of milliseconds since the epoch, and does not include timezone information. 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.

However, the value of 386470800000 corresponds to a different datetime: 1982-04-01 01:00:00.000, whereas other datetime values are represented in the topic as expected. I didn’t find any mention of anything like that among the known bugs.