DateOnly and TimeOnly mapping to SQL Server

Question

When working with dates, I have been using DateTime2 type in SQL Server to store C# DateTime.

.NET 6 has introduced new structs such as DateOnly and TimeOnly. I assume these should be mapped to Date and Time.

Are there any corner cases when the conversion wouldn't be valid? Are these types fully compatible?

Xylol

They can be mapped, whether they should be mapped is probably a matter of opinion. Currently there is nothing out of the box for this in EF Core (discussion here) or SqlClient (e.g. No GetDateOnly method), or razor (<input type="time" /> still uses TimeSpan for the type). As such I personally have decided that DateOnly and TimeOnly do not add enough value for me to introduce all the additional conversions. I will reassess when further support is added. This is of course entirely subjective. YMMV. – Silverware

The new classes still have the same date/time values. The only differences is the new classes add compare methods so you can easily compare two dates/times. – Shena

@Shena DateOnly uses an integer to store the date and so uses only half the size that DateTime uses. – Doehne

Answer 1

When saving time in SQL Server, for instance time(7), with retrieving the column data via a SqlDataReader, the value is returned as a TimeSpan.

This is not an equal conversion. To convert to a TimeOnly instance however, you can parse the stringyfied TimeSpan.

TimeOnly.Parse({timespanvalue}.ToString());

Not extremely elegant, but something to consider when dealing with TimeOnly and sql server time.

Commentative

Answer 2

For conversion of a timespan to TimeOnly you can simply use the api as foreseen by Microsoft on the TimeOnly struct using a static method :

public static TimeOnly FromTimeSpan (TimeSpan timeSpan);

ref https://learn.microsoft.com/en-us/dotnet/api/system.timeonly.fromtimespan?view=net-8.0

As you can see from the link this might only be available as of .NET8

The reason you need this is because if you use the sqldatareader as provided my Microsoft, it returns DateOnly fields that map on Date in sql server to DataTime and TimeOnly fields are mappped to Time in sql server but returned as a TimeSpan by the reader. To actually get the dateonly and timeonly fields you have to use the appropriate apis as foreseen on DateOnly en TimeOnly.

Welbie

Answer 3

You should do two things:

1. Inside your project create a folder "Converter" and a "DateOnlyConverter.cs" class with the following code

   public class DateOnlyConverter : ValueConverter<DateOnly, DateTime> { public DateOnlyConverter() : base(d => d.ToDateTime(TimeOnly.MinValue), d => DateOnly.FromDateTime(d)) { } }

2. In your DbContext class paste the following code

   protected override void ConfigureConventions(ModelConfigurationBuilder builder) {
   builder.Properties() .HaveConversion() .HaveColumnType("date");

   }

It works for me!

Drum

Answer 4

For the ones that are nowadays in .NET 8 and above. Aparently now (if it wasn't in the past versions) is supported the native conversion, so you don't have to worry :)

Flatways