title: Why does EF5 code first use datetime2 when inserting a nullable datetime into the database?
body: <p>I am saving a Cart object to the database that has a nullable datetime. This is the error I get:</p>
  <blockquote>
  <p>The conversion of a datetime2 data type to a datetime data type
  resulted in an out-of-range value.</p>
  </blockquote>
  <p>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.</p>
  <p>I am wondering why EF creates the field one way, but inserts using a different type for the same field.</p>
  <p>This is the domain object:</p>
  <pre><code>using System;
  using System.Collections.Generic;
  
  namespace Core.Domain.Cart
  {
      public partial class Cart : BaseEntity, ILocalizedEntity
      {
          private ICollection&lt;Catalog&gt; _catalogs;
  
          /// &lt;summary&gt;
          /// Gets or sets the name
          /// &lt;/summary&gt;
          public virtual string Name { get; set; }
  
          /// &lt;summary&gt;
          /// Gets or sets the zone identifier
          /// &lt;/summary&gt;
          public virtual int ZoneId { get; set; }
  
          /// &lt;summary&gt;
          /// Gets or sets the brand identifier
          /// &lt;/summary&gt;
          public virtual int BrandId { get; set; }
  
          /// &lt;summary&gt;
          /// Gets or sets the customer type identifier
          /// &lt;/summary&gt;
          public virtual int CustomerTypeId { get; set; }
  
          /// &lt;summary&gt;
          /// Gets or sets the date and time of the opening of a cart
          /// &lt;/summary&gt;
          public virtual DateTime? OpeningDateUtc { get; set; }
  
          /// &lt;summary&gt;
          /// Gets or sets the date and time of the closing of a cart
          /// &lt;/summary&gt;
          public virtual DateTime? ClosingDateUtc { get; set; }
  
          /// &lt;summary&gt;
          /// Gets or sets a value indicating whether the entity is online or not
          /// &lt;/summary&gt;
          public virtual bool IsOnline { get; set; }
  
          /* Truncated for relevance */
      }    
  }
  </code></pre>
  <p>The model:</p>
  <pre><code>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&lt;CartLocalizedModel&gt;
          {            
              public CartModel()
              {
                  Locales = new List&lt;CartLocalizedModel&gt;();
                  Catalogs = new List&lt;CatalogModel&gt;();
                  UnassociatedCatalogs = new List&lt;CatalogModel&gt;();
              }
              [NopResourceDisplayName(&quot;Admin.Carts.Fields.Name&quot;)]
              [AllowHtml]
              public string Name { get; set; }
  
              //Zone dropdown
              [NopResourceDisplayName(&quot;Admin.Carts.Fields.ZoneList&quot;)]
              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(&quot;Admin.Carts.Fields.BrandList&quot;)]
              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(&quot;Admin.Carts.Fields.CustomerTypeList&quot;)]
              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(&quot;Admin.Carts.Fields.OpeningDateUtc&quot;)]
              [UIHint(&quot;DateNullable&quot;)]
              public DateTime? OpeningDateUtc { get; set; }
  
              [NopResourceDisplayName(&quot;Admin.Carts.Fields.ClosingDateUtc&quot;)]
              [UIHint(&quot;DateNullable&quot;)]
              public DateTime? ClosingDateUtc { get; set; }
  
              [NopResourceDisplayName(&quot;Admin.Carts.Fields.IsOnline&quot;)]
              public bool IsOnline { get; set; }
              
              /* Truncated for relevance */
          }
  
  }
  </code></pre>
  <p>So both the <code>OpeningDateUtc</code> and the <code>ClosingDateUtc</code> are of the type DateTime?.</p>
  <p>This is how the database gets generated by EF code first:
  <img src="https://i.stack.imgur.com/kXohX.png" alt="EF generated table" /></p>
  <p>The <code>OpeningDateUtc</code> and <code>ClosingDateUtc</code> are created as a nullable DateTime field.</p>
  <p>So why is it when I save using the <code>IDBContext.SaveChanges()</code>, the SQL generated for the query is:</p>
  <pre><code>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
  </code></pre>
  <p>The interesting part being <code>@4 datetime2(7),@5 datetime2(7)</code>.</p>
  <p>I understand that I could fix this problem by adding a <code>.HasColumnType(&quot;datetime2&quot;)</code> to the cart map, but it doesn't answer why EF5 (and probably older versions) set them to nullable datetime.</p>