SqlDataReader.GetFieldValue<DateTimeOffset>() returns wrong offset for datetimeoffset(0) through datetimeoffset(2)

[priyankatiwari08]

Describe the bug

When reading datetimeoffset columns with scale 0, 1, or 2 from SQL Server using SqlDataReader.GetFieldValue<DateTimeOffset>(), the returned DateTimeOffset value has an incorrect UTC offset. The offset appears to be parsed from the wrong byte position in the TDS response.

Expected behavior

GetFieldValue<DateTimeOffset>() should return a DateTimeOffset with the correct UTC offset matching the stored value in SQL Server.

Actual behavior

The offset is shifted — e.g., a value stored as 2024-01-15 10:30:00.00 +05:30 is returned as 2024-01-15 10:30:00.00 +00:00 (or other incorrect offset depending on data length).

Steps to reproduce

using var connection = new SqlConnection(connectionString);
connection.Open();
using var cmd = connection.CreateCommand();
cmd.CommandText = @"
    DECLARE @dt datetimeoffset(1) = '2024-01-15 10:30:00.0 +05:30';
    SELECT @dt AS Value;";
using var reader = cmd.ExecuteReader();
reader.Read();
var result = reader.GetFieldValue<DateTimeOffset>(0);
Console.WriteLine(result);          // Expected: 2024-01-15 10:30:00.0 +05:30
Console.WriteLine(result.Offset);   // Expected: 05:30:00, Actual: 00:00:00

Environment

• Microsoft.Data.SqlClient version: 6.0.1
• .NET version: .NET 8.0
• SQL Server version: SQL Server 2022
• OS: Windows 11

[github-actions]

🔍 Triage Summary

Check	Result
Issue type	Bug
Environment	All required environment details provided for investigation
Area	Area\Netcore (TDS data-type parsing in the managed .NET path)
Duplicates	Potentially related: dotnet/SqlClient#3011 — "DateTimeOffsetAllScalesTestShouldSucceed Test failures (Values Differ / Ticks must be between Min/Max)", which reports incorrect DateTimeOffset values for scale 2 specifically
Regression	Not indicated (no previously working version mentioned)

Analysis

datetimeoffset values in TDS are encoded as a variable-length time portion (whose byte count depends on the fractional-second scale) followed by 3 date bytes and 2 offset bytes. For scales 0–2 the time portion takes fewer bytes than for higher scales, so a parser that uses a fixed or incorrectly computed byte count will read the UTC-offset bytes from the wrong position, returning a silently incorrect offset. The bug most likely resides in SqlBuffer.get_DateTimeOffset() (SqlBuffer.cs) or in the datetimeoffset byte-parsing logic in TdsParser.cs. The related dotnet/SqlClient#3011 confirms similar byte-level scrambling at scale 2. This is a P1 data-correctness bug — the wrong offset is returned silently, which can cause incorrect time-zone calculations downstream.

Next Steps

• Review dotnet/SqlClient#3011 to check whether the root cause was fully identified and whether the fix covers scales 0 and 1 in addition to 2.
• Assign to the Copilot coding agent to investigate SqlBuffer.get_DateTimeOffset() and the TDS datetimeoffset deserialization path in TdsParser.cs — specifically how the byte offset of the UTC-offset field is computed when fractional-second scale is 0, 1, or 2.
• Ensure both the sync (GetFieldValue<DateTimeOffset>()) and async (GetFieldValueAsync<DateTimeOffset>()) code paths are covered by the fix and by new regression tests (one test per scale 0–2).

Generated by SqlClient Issue Auto-Triage for issue #27 · ● 778.3K · ◷