Support sql_variant containing DateOnly as native SQL date without client conversion

Support sql_variant containing DateOnly as native SQL `date` without client conversion

Problem

When using a SQL Server column of type sql_variant that stores a date, and the CLR value is a DateOnly, the SqlClient does not treat it as a native SQL date. Instead, it fails or requires manual conversion (e.g., via DateOnly.ToDateTime()).

Example:

public class MyEntity
{
    public int Id { get; set; }
    public object? Value { get; set; } // mapped to sql_variant
}

// EF Core model:
modelBuilder.Entity<MyEntity>()
    .Property(e => e.Value)
    .HasColumnType("sql_variant");

// Insert
var e = new MyEntity { Value = DateOnly.FromDateTime(DateTime.Today) };
context.Add(e);
context.SaveChanges(); // Fails or requires pre-conversion

Although SqlClient supports DateOnly for direct parameters with the latest providers (5.1+), it does not automatically handle sql_variant:

EF Core sees the property as object
SqlClient does not infer that the underlying variant value is a date
Causes runtime exceptions or requires manual conversion to DateTime

Expected Behavior

Automatically treat DateOnly as SQL date when assigned to a sql_variant column
Convert the value internally to the appropriate SQL type for variant
Avoid requiring manual DateOnly → DateTime conversion in application code

This would align sql_variant handling for DateOnly with other CLR types:

CLR Type	Current EF / SqlClient behavior
int	OK
string	OK
DateTime	OK
DateOnly	❌ fails for sql_variant

Repro Steps

Create a SQL Server table with a sql_variant column.
Define an EF Core model with the property typed as object?.
Attempt to insert a DateOnly value.
Observe failure or the need to manually convert to DateTime.

Additional Context

Current SqlClient versions support DateOnly for non-variant columns
sql_variant is a generic SQL type, and automatic type inference is missing
This is a limitation, not a crash, but affects EF Core scenarios using flexible variant columns

Labels (suggested)

enhancement
area/SqlClient
needs-investigation

@Harald-PH What error does it fail with?

@p92

It's German. Sorry for that:
Der eingehende Tabular Data Stream (TDS) für das RPC-Protokoll (Remote Procedure Call) ist nicht richtig. Parameter 64 ("㤀㄀.ࠦĈ...Ѐ@p92☀ࠈ�...䀄瀀㤀㌀.ࠦĈ...Ѐ@P94戀
.
.�>...䀄瀀㤀㔀.ࠦĈ...Ѐ@p96☀ࠈ�...䀄瀀㤀㜀.ࠦĈ...Ѐ@p98戀�.�.2Ѐ@P99☀ࠈ�...䀅瀀㄀　　.ࠦለ...Ԁ@p1"): Der 0x00-Datentyp ist unbekannt.

We are using an EAV-aproach. The target field in database is variant null and the value is coming from a DateOnly Field.
I can workaround it by converting the DateOnly field into DateTime. But I am not sure about the consequences yet.

If you can create a simple ADO.NET based repro I can have a look.

Which EF Core version?

Which Microsoft.Data.SqlClient version.

What does EAV approach mean?

If you can create a simple ADO.NET based repro I can have a look.

Which EF Core version?

Which Microsoft.Data.SqlClient version.

        <PackageReference Include="EfCore.SchemaCompare" Version="10.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="10.0.2" />

A sample project will take some time.

What does EAV approach mean?
(https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model)

@Harald-PH I tried this simple console app, but could not make if fail (using Microsoft.Data.SqlClient 6.1,4) - can you modify to make it fail? If not, this could be an EF Core issue.

using Microsoft.Data.SqlClient;

var connectionString = "Server=(localdb)\\mssqllocaldb;Database=Test;Integrated Security=true;Encrypt= false";

using var connection = new SqlConnection(connectionString);

connection.Open();

var command = new SqlCommand(@"
IF OBJECT_ID(N'[dbo].[Eav]', 'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[Eav] (Id INT PRIMARY KEY IDENTITY, Value sql_variant NULL)
END
", connection);

command.ExecuteNonQuery();

var insertCommand = new SqlCommand(@" INSERT INTO dbo.Eav (Value) VALUES (@value)", connection);

insertCommand.Parameters.AddWithValue("@value", new DateOnly(2026, 2, 7));

insertCommand.ExecuteNonQuery();

    var connectionString = "Server=(localdb)\\mssqllocaldb;Database=Test;Integrated Security=true;Encrypt= false";

    using var connection = new SqlConnection(connectionString);

    connection.Open();

    var command = new SqlCommand(@"

IF OBJECT_ID(N'[dbo].[Eav]', 'U') is not null
BEGIN
  drop table [dbo].[Eav]
END
CREATE TABLE [dbo].[Eav] (Id INT PRIMARY KEY IDENTITY, Value sql_variant NULL, Date date NULL)

", connection);

    command.ExecuteNonQuery();

    var insertCommand = new SqlCommand(@" INSERT INTO dbo.Eav (Value,date) VALUES (@value, @value) ", connection);

    insertCommand.Parameters.AddWithValue("@value", new DateOnly(2026, 2, 7));
   
    InsertCommand.ExecuteNonQuery();

You can see that the value created is not a Date, but a DateTime.
I use EF Core Power Tools, and they probably assume that it is a Date and not a DateTime, which leads to the error I reported. You can work around this by using DateTime instead of DateOnly.
At this point, I cannot say whether I will encounter similar problems with my (future) system. That is why I wanted to point out this issue to you.

@ErikEJ

Hi @Harald-PH - Thanks for submitting this issue. I will work with @ErikEJ to figure out what's going on.

Could you explain this statement for me?

You can see that the value created is not a Date, but a DateTime.

Which value are you referring to, where are you seeing the value created with the wrong type, and how can we verify your observations? Is the C# DateOnly being written to the table with an unexpected SQL data type? Is this happening with the sql_variant column, the Date column, both? What SQL data type do you expect the sql_variant to actually be?

I'll mark the issue as Waiting for Customer while you prepare your reply.

I'm not entirely sure what the question is. But I'll try to answer it.
I expect the "Value" column to contain an object of the type of the object specified in the value clause. Without conversion!
In the above case, there is a value of the C# type DateOnly, which corresponds to the db type date.
When you run this code and look at the result in the database, you can see that a datetime object arrives in the database.
The other column ("Date") is for comparison purposes only.

@Harald-PH When you say "look" please let us know what you mean exactly!

I believe he's saying that the sql_variant_property(value, 'basetype') is returning a datetime, not a date. Which implies that the boxed object DateOnly in the sql parameter is being sent with a sqldbtype of datetime.

More complete repro:

using Microsoft.Data.SqlClient;

var connectionString = "Server=(localdb)\\mssqllocaldb;Database=Testproject;Integrated Security=true;Encrypt=false";

using var connection = new SqlConnection(connectionString);

connection.Open();

using var command = new SqlCommand(@"
IF OBJECT_ID(N'[dbo].[Eav]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[Eav] (
    Id INT PRIMARY KEY IDENTITY, 
    VariantValue SQL_VARIANT NULL, 
    DateValue DATE NULL)
END
", connection);

command.ExecuteNonQuery();

command.CommandText = @"DELETE FROM dbo.Eav";

command.ExecuteNonQuery();

var insertCommand = new SqlCommand(@" INSERT INTO dbo.Eav (VariantValue, DateValue) VALUES (@value, @value) ", connection);

insertCommand.Parameters.AddWithValue("@value", new DateOnly(2026, 2, 7));
   
insertCommand.ExecuteNonQuery();

var selectCommand = new SqlCommand(@" SELECT VariantValue, DateValue FROM dbo.Eav ", connection);

var reader = selectCommand.ExecuteReader();

reader.Read();

Console.WriteLine(
    $"VariantValue: {reader["VariantValue"]}, DateValue: {reader["DateValue"]}"
);

var variantValueType = reader.GetFieldType(0);

var dateValueType = reader.GetFieldType(1);

reader.Close();

Console.WriteLine($"VariantValue Type: {variantValueType}, DateValue Type: {dateValueType}");

var variantValueBaseTypeCommand = new SqlCommand(
    @" SELECT SQL_VARIANT_PROPERTY(VariantValue, 'BaseType') AS BaseType FROM dbo.Eav ", connection);

var baseType = variantValueBaseTypeCommand.ExecuteScalar();

Console.WriteLine($"VariantValue BaseType: {baseType}");

@paulmedynski

@paulmedynski Looks like this will be fixed with #4294

@priyankatiwari08

has the submitted repro been verified? (See you closed this)

@priyankatiwari08

Bump Microsoft.Data.SqlClient from 6.1.6 to 7.1.0 (#66)

Navigation Menu

Repository navigation

Support sql_variant containing DateOnly as native SQL date without client conversion #3934

Support sql_variant containing DateOnly as native SQL `date` without client conversion

Problem

Expected Behavior

Repro Steps

Additional Context

Labels (suggested)

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Support sql_variant containing DateOnly as native SQL date without client conversion #3934

Description

Support sql_variant containing DateOnly as native SQL date without client conversion

Problem

Expected Behavior

Repro Steps

Additional Context

Labels (suggested)

Activity

ErikEJ commented on Feb 4, 2026

hhoyer-phs commented on Feb 4, 2026

hhoyer-phs commented on Feb 4, 2026

ErikEJ commented on Feb 4, 2026

ErikEJ commented on Feb 4, 2026

hhoyer-phs commented on Feb 6, 2026

ErikEJ commented on Feb 7, 2026

hhoyer-phs commented on Feb 9, 2026

paulmedynski commented on Feb 10, 2026

hhoyer-phs commented on Feb 10, 2026

ErikEJ commented on Feb 10, 2026

mburbea commented on Feb 10, 2026

ErikEJ commented on Jun 20, 2026

ErikEJ commented on Jun 20, 2026

ErikEJ commented on Aug 24, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Support sql_variant containing DateOnly as native SQL `date` without client conversion