Why does EF5 code first use datetime2 when inserting a nullable datetime into the database?
datetime, datetime2, ef-code-first, entity-framework
Solution
The `DateTime` type in .NET has the same range and precision as `datetime2` in SQL Server. When EF inserts or updates a `datetime` or `datetime2` column in SQL Server it converts the model property to the type that can hold the whole range of `DateTime` in .NET, that's `datetime2`. Converting into `datetime` would fail if the `DateTime` property is not inside the range of `datetime` in SQL Server.
The problem that causes the exception are, by the way, not the two nullable `OpeningDateUtc` and `ClosingDateUtc` columns, but the `CreatedOnUtc` value which is `'0001-01-01 00:00:00'` in your SQL snippet, i.e. `CreatedOnUtc` is apparently not initialized in your model entity. The earliest date that `datetime` in SQL Server can store is in the year 1750, so year 0001 won't fit into the type (but it would fit into `datetime2`).
So, solution is to either set `CreatedOnUtc` to a valid `datetime` value or - as you know - define the types as `datetime2` in your mapping.
But I agree, there would be less confusion if EF would map `DateTime` properties by default to `datetime2`.
Problem
I am saving a Cart object to the database that has a nullable datetime. This is the error I get: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. There are quite a few stackoverflow posts documenting fixes to this problem. However, when code first is creating the database it will create the field as a DateTime (allow nulls). But for some reason, code first tries to insert using a DateTime2 field. I am wondering why EF creates the field one way, but inserts using a different type for the same field. This is the domain object: ``` using System; using System.Collections.Generic; namespace Core.Domain.Cart { public partial class Cart : BaseEntity, ILocalizedEntity { private ICollection<Catalog> _catalogs; /// <summary> /// Gets or sets the name /// </summary> public virtual string Name { get; set; } /// <summary> /// Gets or sets the zone identifier /// </summary> public virtual int ZoneId { get; set; } /// <summary> /// Gets or sets the brand identifier /// </summary> public virtual int BrandId { get; set; } /// <summary> /// Gets or sets the customer type identifier /// </summary> public virtual int CustomerTypeId { get; set; } /// <summary> /// Gets or sets the date and time of the opening of a cart /// </summary> public virtual DateTime? OpeningDateUtc { get; set; } /// <summary> /// Gets or sets the date and time of the closing of a cart /// </summary> public virtual DateTime? ClosingDateUtc { get; set; } /// <summary> /// Gets or sets a value indicating whether the entity is online or not /// </summary> public virtual bool IsOnline { get; set; } /* Truncated for relevance */ } } ``` The model: ``` using FluentValidation.Attributes; using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Web.Mvc; using Telerik.Web.Mvc; namespace Admin.Models.Cart { [Validator(typeof(CartValidator))] public partial class CartModel : BaseNopEntityModel, ILocalizedModel<CartLocalizedModel> { public CartModel() { Locales = new List<CartLocalizedModel>(); Catalogs = new List<CatalogModel>(); UnassociatedCatalogs = new List<CatalogModel>(); } [NopResourceDisplayName("Admin.Carts.Fields.Name")] [AllowHtml] public string Name { get; set; } //Zone dropdown [NopResourceDisplayName("Admin.Carts.Fields.ZoneList")] public SelectList ZoneList { get; set; } //The dropdown with zones public int ZoneId { get; set; } //The selected value of the dropdown once the form is submitted public string ZoneName { get; set; } //The name of the zone to display in data-grid List view. //Brand dropdown [NopResourceDisplayName("Admin.Carts.Fields.BrandList")] public SelectList BrandList { get; set; } //The dropdown with brands public int BrandId { get; set; } //The selected value of the dropdown once the form is submitted public string BrandName { get; set; } //The name of the brand to display in the data-grid List view. //Customer type dropdown [NopResourceDisplayName("Admin.Carts.Fields.CustomerTypeList")] public SelectList CustomerTypeList { get; set; }//The dropdown with CustomerType public int CustomerTypeId { get; set; } //The selected value of the dropdown once the form is submitted public string CustomerTypeName { get; set; } //The name of the CustomerType to display in the data-grid List view. [NopResourceDisplayName("Admin.Carts.Fields.OpeningDateUtc")] [UIHint("DateNullable")] public DateTime? OpeningDateUtc { get; set; } [NopResourceDisplayName("Admin.Carts.Fields.ClosingDateUtc")] [UIHint("DateNullable")] public DateTime? ClosingDateUtc { get; set; } [NopResourceDisplayName("Admin.Carts.Fields.IsOnline")] public bool IsOnline { get; set; } /* Truncated for relevance */ } } ``` So both the `OpeningDateUtc` and the `ClosingDateUtc` are of the type DateTime?. This is how the database gets generated by EF code first: The `OpeningDateUtc` and `ClosingDateUtc` are created as a nullable DateTime field. So why is it when I save using the `IDBContext.SaveChanges()`, the SQL generated for the query is: ``` exec sp_executesql N'update [dbo].[Cart] set [Name] = @0, [ZoneId] = @1, [BrandId] = @2, [CustomerTypeId] = @3, [OpeningDateUtc] = @4, [ClosingDateUtc] = @5, [IsOnline] = @6, [IsReadonly] = @7, [IsPreviewMode] = @8, [CreatedOnUtc] = @9 where ([Id] = @10) ',N'@0 nvarchar(100),@1 int,@2 int,@3 int,@4 datetime2(7),@5 datetime2(7),@6 bit,@7 bit,@8 bit,@9 datetime2(7),@10 int',@0=N'Cart1',@1=7,@2=4,@3=5,@4='2013-01-09 00:00:00',@5='2013-01-18 00:00:00',@6=0,@7=0,@8=1,@9='0001-01-01 00:00:00',@10=1 ``` The interesting part being `@4 datetime2(7),@5 datetime2(7)`. I understand that I could fix this problem by adding a `.HasColumnType("datetime2")` to the cart map, but it doesn't answer why EF5 (and probably older versions) set them to nullable datetime.