CODEWITHSUNDEEP
c#sql-serverdatetime.net-6.0c#-10.0
Korfu
Korfu

Reputation: 833

DateOnly and TimeOnly mapping to SQL Server

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?

Upvotes: 7

Views: 8271

Answers (4)

Jose Manuel Lepe
Jose Manuel Lepe

Reputation: 121

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 :)

enter image description here

enter image description here

Upvotes: 2

user22909981
user22909981

Reputation:

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!

Upvotes: 1

Philip Stuyck
Philip Stuyck

Reputation: 7467

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.

Upvotes: 1

Jorrit Reedijk
Jorrit Reedijk

Reputation: 608

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.

Upvotes: 2

Related Questions