Entity Framework, TimeSpan & SQL Server Time

In the “hey, you cant know everything” category….

A client asked me a question today about mapping TimeSpan to the Time data type in SQL Server.

I didn’t know there was a Time data type in SQL Server. I’m well aware of the Date data type we got in SS2008, but I skipped right past that other new type.

Since I never worked with a Time data type, I never had occasion to use it with EF. So when my client said something about timespan, I thought “that can’t be right”. Timespan is about intervals, not a point in time. Then he said something about the DateTime.TimeOfDay property which returns a timespan and I was all

bsi

So….

step 1:create a table in a SQL Server Database that has a Date field and a Time field.

image

step 2: reverse engineer to code first (I used the EF6 designer)

  public partial class SeparatedDateTime
    {        
        public int Id { get; set; }

        [StringLength(50)]
        public string somestring { get; set; }

        [Column(TypeName = "date")]
        public DateTime justDate { get; set; }

        public TimeSpan justTime { get; set; }
    }
ok so there it is: TimeSpan
step 3: write some code to test it out:
    private static void InsertSeparatedDateTime() {

      using (var context = new DateTimeTester()) {
        context.SeparatedDateTimes.Add(CreateSepDT());
        context.Database.Log = Console.WriteLine;
        context.SaveChanges();
      }
    }

    private static SeparatedDateTime CreateSepDT() {
      var sepDT = new SeparatedDateTime();
      sepDT.somestring = "A Test";
      sepDT.justDate = DateTime.Now.Date;
      sepDT.justTime = DateTime.Now.TimeOfDay;
      return sepDT;
    }
step 4: debug. 
check sepDT.justTime value and it looks like this : it is the milliseconds since midnight in many flavors
image
The log shows me that EF has transformed that value into a time value for the insert:
image
And surely it does the opposite when reading that data back out of the database.
Some caveats:
  • 24 hr time only
  • Presumes you calculated that timespan value correctly
  • All caveats associated with UTC
 
 
 
 
 

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

2 thoughts on “Entity Framework, TimeSpan & SQL Server Time

  1. SQL Server Time is a time-of-day, not a TimeSpan. TimeSpan can hold values of negative timespan, and as you pointed out, greater than 24 hours. SQL Time cannot. The types are not equivalent. The correct mapping is to TimeSpan.TotalSeconds to int.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.