.NET 6 | Support the new BCL DateOnly and TimeOnly structs

New DateOnly and TimeOnly structs are being introduced to .NET 6 as alternatives to DateTime (dotnet/runtime#49036). SqlClient and add support for these as perfect mappings for SQL Server date and time.

Are they likely to have ADO surface area, e.g. DbDataReader.GetDateOnly etc or do you think that providing only GetFieldValue(Async)<T> is appropriate because that wouldn't need any new methods?

Given that the SQL type is date, I suggest making a method DBDataReader.GetDate even if the type it returns in .NET is called DateOnly (which is still being discussed).

@mattjohnsonpint

@mattjohnsonpint I think that would depend on the ranges supported. If it maps directly then I agree if there are any differences we may want to call out the DataOnly type is not the same as sql DATE. Mapping for clr to sql types is already complicated and a bit messy imo I think we should take care not to make it worse for the save of brevity.

They have the exact same range and precision.

Acknowledged.
Looking forward to solidification in design.

Are they likely to have ADO surface area, e.g. DbDataReader.GetDateOnly etc or do you think that providing only GetFieldValue(Async) is appropriate because that wouldn't need any new methods?

I'm not aware of a reason to add new GetDate/GetTime APIs to DbDataReader - the generic GetFieldValue (and Async) should be sufficient for retrieving the new types (unless you guys have a reason?).

No strong opinions here, but a reason would be discoverability for those used to APIs like DbDataReader.GetDateTime.

No strong opinions here, but a reason would be discoverability for those used to APIs like DbDataReader.GetDateTime.

That's true, but don't think it's sufficient... Note how there are no async equivalents for the Get* methods (e.g. no GetDateTimeAsync), and users must use the generic GetFieldValueAsync. Basically I think the Get* methods are a bit of a remnant from the days before generics existed.

Please note that the final names for these types are DateOnly and TimeOnly and that they are now merged into the main branch for the next version of .NET 6 (preview 4 likely). Please rename the title of this issue accordingly. Thanks.

FYI preview4 is out and these types are now available (I've added support in Npgsql). If you guys can add support at your level, I can do that in EF Core for SQL Server as well.

"If you guys can add support at your level, I can do that in EF Core for SQL Server as well."

What's the current status here? It seems, DateOnly support ist not given yet (in NET 6 preview 6)

Any workarounds for that yet (ConfigureConventions?) except not using DateOnly in models?

Did you mean to ask in EFCore?

Status in SqlClient is that it's waiting for the NET6 release, CI update, new project targets, then work, then testing, then release which is every 6 months.

Any update on this ? is this included in Ef core 7 previews ?

No. There is no update. There is still no net6 build target for this library and that is still required to begin work on the feature. If there were an update I'm sure someone would post about it here.

You don't really need support for it at this level. You could handle it all in the ORM. (That's what my ORM did.)

I'm not even sure what support would look like. Suddenly changing columns that were materialized as DateTime to be DateOnly or TimeOnly would be a breaking change.

You may not need it, but it's handier then everyone adding their own value converters. I don't see why there would need to be a breaking change. If a user changes their code to use one of the new objects, then they are changing their own functionality.

I'm not even sure what support would look like. Suddenly changing columns that were materialized as DateTime to be DateOnly or TimeOnly would be a breaking change.

I think we discussed this above; support would mean allowing asking GetFieldValue<T> for a DateOnly/TimeOnly, and allowing SqlParameter.Value to be DateOnly/TimeOnly. This is completely non-breaking, and would also allow seamless support in ORMs layered over SqlClient.

These types are now a standard part of the .NET API, database drivers really should just support them at this point. The lack of native support at the ADO.NET layer also causes various issues and difficulties at upper (ORM) layers).

When you call GetValues, can DateOnly appear in the array for Date columns?

If yes, it's a breaking change and people will be mad.

If no, it doesn't faithfully represent what's in the database and people will be mad.

Does it make sense to have a switch that controls this? Maybe, but it would be non-obvious and that's close to not existing.

Hence my concern about not knowing what this is going to look like.

When you call GetValues, can DateOnly appear in the array for Date columns?

Based on previous comments, I think they are only going to support GetFieldValue(Async)<T>:

No strong opinions here, but a reason would be discoverability for those used to APIs like DbDataReader.GetDateTime.

That's true, but don't think it's sufficient... Note how there are no async equivalents for the Get* methods (e.g. no GetDateTimeAsync), and users must use the generic GetFieldValueAsync. Basically I think the Get* methods are a bit of a remnant from the days before generics existed.

@Grauenwolf

When you call GetValues, can DateOnly appear in the array for Date columns?
If yes, it's a breaking change and people will be mad.
If no, it doesn't faithfully represent what's in the database and people will be mad.

@Grauenwolf, the Npgsql, Microsoft.Data.Sqlite, and MySqlConnector drivers have all implemented this change. At least for the first two, this allows you to send DateOnly values in DbParameter, and use GetFieldValue to read them. This isn't a breaking change, and absolutely nobody got mad that GetValues hasn't changed. Modern usage of ADO.NET generally uses GetFieldValue anyway - it's generic, and so doesn't require additional casting and also doesn't box value type. In my experience supporting Npgsql users, not many still use the non-generic read APIs (GetValue/GetValues) - these really make sense mainly in fully dynamic scenarios, where your program sends arbitrary SQL queries and doesn't know what types will be coming back.

Sure, it's possible to add an opt-in to also change the "default" CLR type for dates from DateTime to DateOnly; that would indeed bring this change to the non-generic APIs as well. I've personally not seen one request for it so far, so I'd really start off with the non-breaking change (parameters and generic GetFieldValue), and see from there.

@roji

@roji

the Npgsql, Microsoft.Data.Sqlite, and MySqlConnector drivers have all implemented this change. At least for the first two, this allows you to send DateOnly values in DbParameter, and use GetFieldValue to read them. This isn't a breaking change, and absolutely nobody got mad that GetValues hasn't changed.

... still you changed it in npgsql 10 which broke my code. (as my code assumed the npgsql 9 types were still in play).

I must say the inconsistency between reader.GetValue() and reader.GetFieldValue() is pretty bad here: as I can save DateOnly values but fetching them with a reader and then use GetValues() gives me DateTime typed values, while using reader.GetFieldValue() will give me DateOnly values; this isn't the case by any other type; all other types are consistent across these methods.

I don't expect this to be changed, just wanted to mention that, hey, I'm one of the people who ran into this and can throw away a day's work. :)

The main issue is that if fixed types are used in an entity class, e.g. a DateOnly and TimeOnly property in a class, mapped to Date and Time fields in the database through some layer, the mapping system has to make sure a value in the object can be written to the table and a value read from the table can be placed in the object. We use reader.GetValues() to get the values asap from the reader, and close the reader quickly. A typed projection generated from a lambda directly on the reader will use the reader.GetFieldValue(), this is for linq queries etc. but not for entity fetches.

The inconsistency is problematic; I can live with GetValues() returning a DateOnly type always, I have to make sure the mapped entity property is of type DateOnly as well. However if one projection pipeline using GetFieldValue will produce DateOnly and another one will produce DateTime values, then a problem arises: the only solution then is to go for the type that has always worked: DateTime, but this limits the expression freedom for the entity type: the property can't be of type DateOnly.

@FransBouma

Hey @FransBouma 👋

Can you help me understand exactly where the inconsistency is which you're seeing? Both NpgsqlDataReader.GetValue() and GetValues() are non-generic methods which return/populate object - these have both been changed to return .NET DateOnly for a PostgreSQL date. GetFieldValue<T>() returns whatever you ask of it: you can ask it for a DateOnly or a DateTime, Npgsql supports either one. See the quick code sample below that shows all this working.

If there's some sort of inconsistency/bug we can definitely fix it for 10.0.1.

BTW maybe this is better discussed on the Npgsql repo, do you want to open an issue there?

Code sample

var builder = new NpgsqlDataSourceBuilder("Host=localhost;Username=test;Password=test");
await using var dataSource = builder.Build();
await using var connection = await dataSource.OpenConnectionAsync();

await using var command = new NpgsqlCommand("SELECT '2020-03-01'::date", connection);
await using var reader = await command.ExecuteReaderAsync();
await reader.ReadAsync();

Console.WriteLine(reader.GetValue(0).GetType().Name); // DateOnly

var values = new object[1];
reader.GetValues(values);
Console.WriteLine(values[0].GetType().Name); // DateOnly

Console.WriteLine(reader.GetFieldValue<DateOnly>(0).GetType().Name); // DateOnly
Console.WriteLine(reader.GetFieldValue<DateTime>(0).GetType().Name); // DateTime

@roji

hey @roji !

Sorry for the confusion, I hope to clear it up: I referred to npgsql because in your post #1009 (comment) you mention that it's not really needed to change GetValue/GetValues behavior regarding DateOnly/TimeOnly while (albeit sometime later) you did change it for npgsql.

The inconsistency is in Microsoft.Data.SqlClient, not npgsql, where it is consistent. The change you made in npgsql 10 does illustrate the problems that arise however: a breaking change in the ado.net provider means a breaking change in the middleware using the ado.net provider. If these are version-coupled (like entity framework) you won't notice. However many database middleware isn't: there the ado.net provider is loosely coupled e.g. through the DbProviderFactory. (I'm glad you added a switch to get the old behavior back in 10.0.1 btw!)

ADO.NET providers map database types to .net types and it's key to have a consistent mapping for all supported types, so a user knows what to expect when calling a function on a datareader: they know for database type A with precision/scale P and S they'll get a value V with type T back when calling a method on the datareader, doesn't matter which one!

Here, in Microsoft.Data.SqlClient that's not the case for DateOnly/TimeOnly. This is BAD. Changing the behavior for DateOnly/TimeOnly is a breaking change but IMHO this should be handled as follows:

keep the old behavior as the default. (So map to DateTime and TimeSpan). The main reason is that users of a database middleware aren't aware of the breaking change nor should they be; they're using a middleware!
add the new mapping (to DateOnly and TimeOnly) consistently across all methods and add a switch to enable this behavior, which basically means, switching to another mapping table for the .net <-> db type mapping

I've seen some breaking changes in ado.net providers in the past 23 years I'm now writing ORMs (Firebird, ODP.NET, Npgsql) and it's always a nightmare. The middleware developer might be able to cope with it, the users are caught off guard, as the breaking change ripples through.

Thanks for considering

I referred to npgsql because in your post #1009 (comment) you mention that it's not really needed to change GetValue/GetValues behavior regarding DateOnly/TimeOnly while (albeit sometime later) you did change it for npgsql.

The inconsistency is in Microsoft.Data.SqlClient, not npgsql, where it is consistent.

Ah, I understand. Yeah, it seemed that enough time has passed for DateOnly/TimeOnly to propagate through the ecosystem, and also crucially, the new version of Npgsql no longer supports old TFMs where DateOnly/TimeOnly didn't yet exist (otherwise we'd have the same version of Npgsql returning different things based on TFM, which seems bad). FWIW we've had various debates between the maintainers if this breaking change was worth it, not everyone necessarily agrees; but I admit I'm more on the side of moving forward with the ecosystem, and DateOnly/TimeOnly are now the canonical/standard ways to represent date and time, so the change made sense to me.

But regardless, I'm still not exactly sure where SqlClient isn't consistent - can you provide a quick code sample illustrating the problem? AFAICT SqlClient always returns DateTime by default, except if you call SqlDataReader.GetFieldValue<DateOnly>(), in which case it returns DateOnly (see code sample just below which plays around with this). But I'm sure I'm missing something - can you point out the inconsistency?

SqlClient code sample

await using var connection = new SqlConnection("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false");
await connection.OpenAsync();

// Read side
await using (var command = new SqlCommand("SELECT CAST('2020-03-01' AS date)", connection))
{
    await using var reader = await command.ExecuteReaderAsync();
    await reader.ReadAsync();

    Console.WriteLine(reader.GetFieldType(0).Name); // DateTime
    Console.WriteLine(reader.GetValue(0).GetType().Name); // DateTime

    var values = new object[1];
    reader.GetValues(values);
    Console.WriteLine(values[0].GetType().Name); // DateTime

    Console.WriteLine(reader.GetFieldValue<DateOnly>(0).GetType().Name); // DateOnly
    Console.WriteLine(reader.GetFieldValue<DateTime>(0).GetType().Name); // DateTime
}

// Write side
await using (var command = new SqlCommand("SELECT @dateonly, @datetime", connection))
{
    command.Parameters.AddWithValue("@dateonly", new DateOnly(2020, 3, 1));
    command.Parameters.AddWithValue("@datetime", new DateTime(2020, 3, 1));

    await using var reader = await command.ExecuteReaderAsync();
    await reader.ReadAsync();

    Console.WriteLine(reader.GetFieldType(0).Name); // DateOnly
    Console.WriteLine(reader.GetFieldType(1).Name); // DateTime
}

I'm glad you added a switch to get the old behavior back in 10.0.1 btw!

FWIW we'll probably end up not introducing a new switch, but rather just publish a fragment that users can paste in to alter Npgsql's mapping (we have APIs for that), to revert to DateTime/TimeSpan by default. That's just a minor detail (the important thing is that there'll be a way to revert back - even on 10.0.0 BTW).

ADO.NET providers map database types to .net types and it's key to have a consistent mapping for all supported types, so a user knows what to expect when calling a function on a datareader: they know for database type A with precision/scale P and S they'll get a value V with type T back when calling a method on the datareader, doesn't matter which one!

That would certainly be ideal, but the unfortunate reality is that database types are, well, a total mess... SQL Server and PostgreSQL have completely different types around timestamps: SQL Server has datetime, datetime2 and datetimeoffset; PG has timestamp (timestamp with unknown/implicit timezone) and timestamptz (timestamp assumed to be UTC), with nothing that can hold a timezone. And don't even mention textual types (varchar/nvarchar with max or otherwise on SQL Server, compared to text on PG). IIRC Oracle doesn't have time (only interval), and of course SQLite has nothing at all...

So if an application or layer is handling both database types and the mapping between them and .NET types, I don't think they have a choice but to know about each database and how .NET types map to database types for that database. Though of course, adding a driver version to the mix (Npgsql 10 vs. older versions) definitely adds more complexity, I do get that 100%.

But regardless, I'm still not exactly sure where SqlClient isn't consistent - can you provide a quick code sample illustrating the problem? AFAICT SqlClient always returns DateTime by default, except if you call SqlDataReader.GetFieldValue(), in which case it returns DateOnly (see code sample just below which plays around with this). But I'm sure I'm missing something - can you point out the inconsistency?

As there's no implicit conversion from DateTime to DateOnly and vice versa, there's explicit code added to make GetFieldValue<DateOnly>() possible if the underlying type is actually DateTime (as reported by GetFieldType(ordinal)). Yes I know the code internally doesn't convert from DateTime to DateOnly but converts the raw data directly to DateOnly, but from the outside it looks like that. The core issue is that the answer to "to what type does SQL Server's type 'Date' map to in the .net space using Microsoft.Data.SqlClient?" is "it depends". While that's classic database-esk, it's also not what you want: you want a clear cut answer: a single type you can rely on the ado.net provider will return when a value comes back from the database: that value will be of that particular type and all code can depend on that. Here that's not clear cut; Microsoft.Data.SqlClient has 'some' support for DateOnly but not all the way; it didn't make the choice, only ... partly, which is basically return DateOnly.FromDateTime(GetValue(ordinal)).

db types are a mess

True, but within the scope of a single ADO.NET provider they should be consistent. Of course any ADO.NET provider makes their own choice, hence we have e.g. the lovely type disaster in ODP.NET for int32/int64 which even differ if you use it with EF Core or native!

Coming back to the point: as the Microsoft.Data.SqlClient team made some effort to have some support for DateOnly (GetFieldValue, datatable) but not make the tough choice to simply define the mapping properly, it's a confusing mess. But like I said, I don't expect it to change. At least not anytime soon.

Ah, I see - the inconsistency you're pointing out is between what's returned by SqlDataReader.GetFieldType() (DateTime, also the default as returned by non-generic GetValue()) and using the generic GetFieldValue<DateOnly>() to fetch a DateOnly, as requested/wanted by the user.

The core issue is that the answer to "to what type does SQL Server's type 'Date' map to in the .net space using Microsoft.Data.SqlClient?" is "it depends".

I'd phrase it slightly different - Sqlclient maps SQL Server date to DateTime by default, but allows also mapping it to DateOnly (non-default) if the user desires that (but I see what you mean). FWIW Npgsql (and I think most other ADO.NET providers) also has various cases where there's one default type, but the you can use GetFieldValue<T>() to get another non-default type (see these docs, where there's a column for the default and then another for other options).

I'll even point out that before Npgsql 10, Npgsql had the exact same behavior: the default for date was DateTime (so that's what GetFieldType() and non-generic GetValue() returned), but you could still request a DateOnly via generic GetFieldValue<T>(). And with Npgsql 10 the situation is the same, only reversed: you get DateOnly by default, but can still ask for a DateTime (so it's "inconsistent" in that sense).

So I'm basically pointing out that this situation isn't unique to SqlClient or date/DateOnly, and I'm not sure it's right to call this situation "inconsistent": it's just that there's always a single default value, and then optionally additional non-default ones.

Navigation Menu

Repository navigation

.NET 6 | Support the new BCL DateOnly and TimeOnly structs #1009

29 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

.NET 6 | Support the new BCL DateOnly and TimeOnly structs #1009

Description

Activity

Wraith2 commented on Mar 25, 2021

mattjohnsonpint commented on Mar 25, 2021

Wraith2 commented on Mar 25, 2021

mattjohnsonpint commented on Mar 25, 2021

cheenamalhotra commented on Mar 25, 2021

roji commented on Mar 26, 2021

mattjohnsonpint commented on Mar 26, 2021

roji commented on Mar 27, 2021

mattjohnsonpint commented on Apr 15, 2021

roji commented on May 29, 2021

CleanCodeX commented on Aug 2, 2021

Wraith2 commented on Aug 2, 2021

29 remaining items

nabenzine commented on Sep 23, 2022

Wraith2 commented on Sep 23, 2022

Grauenwolf commented on Sep 23, 2022

MisinformedDNA commented on Sep 23, 2022

roji commented on Sep 23, 2022

Grauenwolf commented on Sep 25, 2022

MisinformedDNA commented on Sep 25, 2022

roji commented on Sep 25, 2022

FransBouma commented on Nov 27, 2025

roji commented on Nov 27, 2025

FransBouma commented on Nov 28, 2025

roji commented on Nov 28, 2025

FransBouma commented on Nov 28, 2025

roji commented on Nov 28, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions