Support sql_variant containing DateOnly as native SQL date without client conversion

[Harald-PH]

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

1. Create a SQL Server table with a sql_variant column.
2. Define an EF Core model with the property typed as object?.
3. Attempt to insert a DateOnly value.
4. 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

[ErikEJ]

@Harald-PH What error does it fail with?

[Harald-PH]

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.

[Harald-PH]

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.

[ErikEJ]

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

Which EF Core version?

Which Microsoft.Data.SqlClient version.

[ErikEJ]

What does EAV approach mean?

[Harald-PH]

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)

[ErikEJ]

@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();

[Harald-PH]

    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. 

[paulmedynski]

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.

[Harald-PH]

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.

[ErikEJ]

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

[mburbea]

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.