The DateOnly and TimeOnly types are new additions to .NET that were added in .NET 6 in 2021.

DateOnly can be useful to properly represent for example date of birth or invoice date.

The new types map directly to the existing date and time data types that were added to SQL Server in 2008.

Steve Gordon - MVP has a nice blog post about using the types.

Trying out the new support in SqlClient

Until recently trying to use these types against SQL Server/Azure SQL with [Microsoft.Data.SqlClient] failed, as SqlClient was not built for .NET 6. This is no longer the case and thanks to a recent Pull Request, you can now use DateOnly (and TimeOnly) with the latest SqlClient 5.1 preview 2

To try it out in your .NET 6 or later project, start by adding an explicit reference Microsoft.Data.SqlClient to version 5.1 (currrently in preview)

<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.0-preview2.22314.2" />

If you do not use the updated package, you will get an error message similar to: No mapping exists from object type System.DateOnly to a known managed provider native type.

Now we can test the new types:

First, create a table with a date column:

using Microsoft.Data.SqlClient;

using var connection = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=DateOnlyTest;Integrated Security=true;Encrypt=False");
connection.Open();

using var command = connection.CreateCommand();

command.CommandText = @"
IF NOT EXISTS (SELECT * 
               FROM INFORMATION_SCHEMA.TABLES 
               WHERE TABLE_SCHEMA = 'dbo' 
                 AND TABLE_NAME = 'TestTable') 
    CREATE TABLE [dbo].[TestTable](
	    [Id] [bigint] NOT NULL,
	    [InvoiceDate] [date] NOT NULL
)";
command.ExecuteNonQuery();

Then insert some data using parameterised SQL:

command.CommandText = "INSERT INTO dbo.TestTable (Id, InvoiceDate) VALUES (@p1, @p2)";
command.Parameters.Add(new SqlParameter("@p1", 1));
command.Parameters.Add(new SqlParameter("@p2", new DateOnly(1964, 7, 25)));
command.ExecuteNonQuery();

And finally get some data back using a SqlDataReader:

command.Parameters.Clear();
command.CommandText = "SELECT [InvoiceDate] FROM dbo.TestTable WHERE [InvoiceDate] = @p1";
command.Parameters.Add(new SqlParameter("@p1", new DateOnly(1964, 7, 25)));
using var reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
    var date = await reader.GetFieldValueAsync<DateOnly>(0);
    Console.WriteLine(date);
}

Happy coding!

Coming in EF Core?

Support in Entity Framework Core is planned for EF Core 8 (and maybe will be backported via a community contribution). If you are interested in this, make sure to upvote the related issue.