asp.net-mvc 将 datetime2 数据类型转换为 datetime 数据类型导致值超出范围

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

提问by Cameron

I have the following code in my HomeController:

我的 HomeController 中有以下代码：

public ActionResult Edit(int id)
{
    var ArticleToEdit = (from m in _db.ArticleSet where m.storyId == id select m).First();
    return View(ArticleToEdit);
}

[ValidateInput(false)]
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Edit(Article ArticleToEdit)
{
    var originalArticle = (from m in _db.ArticleSet where m.storyId == ArticleToEdit.storyId select m).First();
    if (!ModelState.IsValid)
        return View(originalArticle);

    _db.ApplyPropertyChanges(originalArticle.EntityKey.EntitySetName, ArticleToEdit);
    _db.SaveChanges();
    return RedirectToAction("Index");
}

And this is the view for the Edit method:

这是 Edit 方法的视图：

<% using (Html.BeginForm()) {%>

    <fieldset>
        <legend>Fields</legend>
        <p>
            <label for="headline">Headline</label>
            <%= Html.TextBox("headline") %>
        </p>
        <p>
            <label for="story">Story <span>( HTML Allowed )</span></label>
            <%= Html.TextArea("story") %>
        </p>
        <p>
            <label for="image">Image URL</label>
            <%= Html.TextBox("image") %>
        </p>
        <p>
            <input type="submit" value="Post" />
        </p>
    </fieldset>

<% } %>

When I hit the submit button I get the error: {"The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.\r\nThe statement has been terminated."}Any ideas what the problem is? I'm assuming that the edit method is trying to update the posted value in the DB to the edited on but for some reason it's not liking it... Although I don't see why the date is involved as it's not mentioned in the controller method for edit?

当我点击提交按钮时，我收到错误消息：{"The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.\r\nThe statement has been terminated."}任何想法是什么问题？我假设编辑方法正在尝试将数据库中的发布值更新为已编辑，但由于某种原因它不喜欢它......虽然我不明白为什么涉及日期，因为它没有在用于编辑的控制器方法？

采纳答案by Jacob

The issue is that you're using ApplyPropertyChangeswith a model object that has only been populated with data in the form (headline, story, and image). ApplyPropertyChangesapplies changes to all properties of the object, including your uninitialized DateTime, which is set to 0001-01-01, which is outside of the range of SQL Server's DATETIME.

问题是您使用ApplyPropertyChanges的模型对象仅填充了表单中的数据（标题、故事和图像）。 ApplyPropertyChanges将更改应用于对象的所有属性，包括DateTime设置为 0001-01-01 的uninitialized ，这超出了 SQL Server 的DATETIME.

Rather than using ApplyPropertyChanges, I'd suggest retrieving the object being modified, change the specific fields your form edits, then saving the object with those modifications; that way, only changed fields are modified. Alternately, you can place hidden inputs in your page with the other fields populated, but that wouldn't be very friendly with concurrent edits.

与其使用ApplyPropertyChanges，我建议检索正在修改的对象，更改表单编辑的特定字段，然后使用这些修改保存对象；这样，只会修改更改的字段。或者，您可以在页面中放置隐藏的输入并填充其他字段，但这对并发编辑不是很友好。

Update:

更新：

Here's an untested sample of just updating some fields of your object (this is assuming you're using LINQ to SQL):

这是一个未经测试的示例，仅更新对象的某些字段（假设您使用的是 LINQ to SQL）：

var story = _db.ArticleSet.First(a => a.storyId == ArticleToEdit.storyId);
story.headline = ArticleToEdit.headline;
story.story = ArticleToEdit.story;
story.image = ArticleToEdit.image;
story.modifiedDate = DateTime.Now;
_db.SubmitChanges();

回答by Sanjay Kumar Madhva

This is a common error people face when using Entity Framework. This occurs when the entity associated with the table being saved has a mandatory datetime field and you do not set it with some value.

这是人们在使用实体框架时面临的常见错误。当与要保存的表关联的实体具有强制日期时间字段并且您没有为其设置某个值时，就会发生这种情况。

The default datetime object is created with a value of 01/01/1000and will be used in place of null. This will be sent to the datetime column which can hold date values from 1753-01-01 00:00:00onwards, but not before, leading to the out-of-range exception.

默认日期时间对象是使用值创建的01/01/1000，并将用于代替空值。这将被发送到日期时间列，该列可以从1753-01-01 00:00:00以后但不能保存日期值，从而导致超出范围的异常。

This error can be resolved by either modifying the database field to accept null or by initializing the field with a value.

可以通过修改数据库字段以接受 null 或使用值初始化该字段来解决此错误。

回答by Andrew Orsich

DATETIMEsupports 1753/1/1 to "eternity" (9999/12/31), while DATETIME2support 0001/1/1 through eternity.

DATETIME支持 1753/1/1 到“永恒”（9999/12/31），同时 DATETIME2支持 0001/1/1 到永恒。

Msdn

msdn

Answer:I suppose you try to save DateTimewith '0001/1/1' value. Just set breakpoint and debug it, if so then replace DateTimewith nullor set normal date.

答：我想您尝试DateTime使用“0001/1/1”值进行保存。只需设置断点和调试它，如果这样的话更换DateTime用null或组正常日期。

回答by sky-dev

This one was driving me crazy. I wanted to avoid using a nullable date time (DateTime?). I didn't have the option of using SQL 2008's datetime2 type either (modelBuilder.Entity<MyEntity>().Property(e => e.MyDateColumn).HasColumnType("datetime2");).

这让我发疯。我想避免使用可为空的日期时间 ( DateTime?)。我也没有选择使用 SQL 2008 的 datetime2 类型 ( modelBuilder.Entity<MyEntity>().Property(e => e.MyDateColumn).HasColumnType("datetime2");)。

I eventually opted for the following:

我最终选择了以下内容：

public class MyDb : DbContext
{
    public override int SaveChanges()
    {
        UpdateDates();
        return base.SaveChanges();
    }

    private void UpdateDates()
    {
        foreach (var change in ChangeTracker.Entries<MyEntityBaseClass>())
        {
            var values = change.CurrentValues;
            foreach (var name in values.PropertyNames)
            {
                var value = values[name];
                if (value is DateTime)
                {
                    var date = (DateTime)value;
                    if (date < SqlDateTime.MinValue.Value)
                    {
                        values[name] = SqlDateTime.MinValue.Value;
                    }
                    else if (date > SqlDateTime.MaxValue.Value)
                    {
                        values[name] = SqlDateTime.MaxValue.Value;
                    }
                }
            }
        }
    }
}

回答by Dongolo Jeno

You can also fix this problem by adding to model (Entity Framework version >= 5)

您还可以通过添加到模型来解决此问题（实体框架版本 >= 5）

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime CreationDate { get; set; }

回答by Patrick

If you have a column that is datetime and allows null you will get this error. I recommend setting a value to pass to the object before .SaveChanges();

如果您有一列是日期时间并允许为空，您将收到此错误。我建议在 .SaveChanges(); 之前设置一个值来传递给对象。

回答by Aleksandr Khomenko

I got this error after I changed my model (code first) as follows:

在我更改模型（代码优先）后出现此错误，如下所示：

public DateTime? DateCreated

to

到

public DateTime DateCreated

Present rows with null-value in DateCreated caused this error. So I had to use SQL UPDATE Statementmanually for initializing the field with a standard value.

在 DateCreated 中显示具有空值的行导致此错误。所以我不得不手动使用SQL UPDATE 语句来用标准值初始化字段。

Another solution could be a specifying of the default value for the filed.

另一种解决方案可能是指定字段的默认值。

回答by Ogglas

It looks like you are using entity framework. My solution was to switch all datetime columns to datetime2, and use datetime2 for any new columns, in other words make EF use datetime2 by default. Add this to the OnModelCreating method on your context:

看起来您正在使用实体框架。我的解决方案是将所有日期时间列切换到 datetime2，并对任何新列使用 datetime2，换句话说，让 EF 默认使用 datetime2。将此添加到您上下文中的 OnModelCreating 方法中：

modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));

That will get all the DateTime and DateTime? properties on all the entities in your model.

那会得到所有的DateTime和DateTime吗？模型中所有实体的属性。

回答by StinkyCat

In my case, in the initializer from the class I was using in the database's table, I wasn't setting any default value to my DateTime property, therefore resulting in the problem explained in @Andrew Orsich' answer. So I just made the property nullable. Or I could also have given it DateTime.Now in the constructor. Hope it helps someone.

就我而言，在我在数据库表中使用的类的初始值设定项中，我没有为我的 DateTime 属性设置任何默认值，因此导致了@Andrew Orsich 的回答中解释的问题。所以我只是让属性可以为空。或者我也可以在构造函数中给它 DateTime.Now 。希望它可以帮助某人。

回答by Willy David Jr

I had the same problem, unfortunately, I have two DateTime property on my model and one DateTime property is null before I do SaveChanges.

我遇到了同样的问题，不幸的是，我的模型上有两个 DateTime 属性，并且在我执行 SaveChanges 之前，一个 DateTime 属性为空。

So make sure your model has DateTime value before saving changes or make it nullable to prevent error:

因此，请确保您的模型在保存更改之前具有 DateTime 值或使其可为空以防止错误：

public DateTime DateAdded { get; set; }   //This DateTime always has a value before persisting to the database.
public DateTime ReleaseDate { get; set; }  //I forgot that this property doesn't have to have DateTime, so it will trigger an error

So this solves my problem, its a matter of making sure your model date is correct before persisting to the database:

所以这解决了我的问题，这是在持久化到数据库之前确保模型日期正确的问题：

public DateTime DateAdded { get; set; }
public DateTime? ReleaseDate { get; set; }