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

So….

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

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

The log shows me that EF has transformed that value into a time value for the insert:

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