DateTimeOffset incorrectly re-converted to Gregorian calendar for historical dates

[rodireich]

Driver version

12.8.1.jre11 (latest)

SQL Server version

Microsoft SQL Azure (RTM) - 12.0.2000.8
Oct 2 2024 11:51:41
Copyright (C) 2022 Microsoft Corporation

Client Operating System

linux/macOS

JAVA/JVM version

21

Table schema

CREATE TABLE [dbo].[bc-test] (
    [Id]  INT                IDENTITY (1, 1) NOT NULL,
    [dob] DATETIMEOFFSET (7) NULL,
    CONSTRAINT [PK_bc-test] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Problem description

This is the same problem that was reported in #2246 ,
Which I believe was incorrectly closed:
A date inserted to the above table that is prior to Gregorian cutoff date (e.g insert into dbo.bc-test values ('1500-12-16 00:00:00.0000000+08:00');.

The reason I think this is not expected is because dates in SQL, and in particular in Sql Server are already stored in Gregorian calendar dates as documented:

Calendar Gregorian

By treating historical dates as Julian rather, as it behaves when DateTimeOffset is converted to OffsetDateTime, a Gregorian date is re-converted to Gregorian, returning a value that is off by a few days.

Expected behavior

Historical dates should behave similarly how a date in present time retains the same value when read into java.time.OffsetDateTime,
e.g
insert into dbo.bc-test values ('2024-12-16 00:00:00.0000000+08:00');
will yield
2024-12-16T00:00:00.000000-08:00

Actual behavior

When converted in java to a java.time.OffsetDateTime will go through conversion to Gregorian date which will yield the value 1500-12-26T00:00:00.000000-07:53.

note the 26 instead of the 16 that was inserted

Error message/stack trace

n/a

Any other details that can be helpful

A more acute offset occurs when getting close to minimum value - 0001-01-01,
There's a range of hours, depending on timezone in which the conversion to Gregorian actually sends us back a day or two into BC.
0001-01-01 00:00:00.0000000 +00:00 in MSSQL is being incorrectly interpreted to 0001-12-30T00:00:00.000000Z BC

I would like to kindly ask you to reconsider this issue as it creates a problem for our users.

JDBC trace logs

n/a

[marcinar]

The fix for this seems incomplete. The commit 74a5cb8 only fixes the getObject(OffsetDateTime.class) case. The method setObject(offsetDateTimeInstance) has the same problem.

Doing getObject -> setObject can now change the value of an OffsetDateTime field.

[machavan]

Hi @marcinar

Thanks for reporting it, we are looking into it.