将datetime2数据类型转换为datetime数据类型会导致超出范围的值

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:

这是编辑方法的视图:

<% 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?

当我点击submit按钮时，我得到了错误:{“将datetime2数据类型转换为datetime数据类型导致了一个超出范围的值。声明已被终止。有什么问题吗?我假设编辑方法试图将DB中的已发布值更新为on，但出于某种原因它不喜欢它……虽然我不明白为什么会涉及到日期，因为在用于编辑的控制器方法中没有提到日期?

16 个解决方案

#1

The issue is that you're using ApplyPropertyChanges with a model object that has only been populated with data in the form (headline, story, and image). ApplyPropertyChanges applies 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，超出了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();

#2

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.

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

The default datetime object is created with a value of 01/01/1000 and 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:00 onwards, but not before, leading to the out-of-range exception.

默认的datetime对象创建时的值为01/01/1000，将用它来代替null。这将被发送到datetime列，该列可以保存从1753-01-01 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或使用值初始化字段来解决此错误。

#3

DATETIME supports 1753/1/1 to "eternity" (9999/12/31), while DATETIME2 support 0001/1/1 through eternity.

DATETIME支持1753/1到“eternal”(9999/12/31)，而DATETIME2则支持0001/1/1到forever。

Msdn

Msdn

Answer: I suppose you try to save DateTime with '0001/1/1' value. Just set breakpoint and debug it, if true than replace DateTime with null or set normal date.

答:我想您尝试用“0001/1/1”的值来保存DateTime。只需设置断点并调试它，如果为真，则将DateTime替换为null或设置正常日期。

#4

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类型的选项(modelbuild . entity ()。属性(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;
                    }
                }
            }
        }
    }
}

#5

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();

如果您有一个列是datetime并允许null，您将会得到这个错误。我建议设置一个值，以便在.SaveChanges()之前传递到对象;

#6

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

您还可以通过添加模型来解决这个问题(实体框架版本>= 5)

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

#7

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

我更改模型(代码为first)后得到如下错误:

public DateTime? DateCreated

to

来

public DateTime DateCreated

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

在DateCreated中显示具有null值的行会导致此错误。因此，我必须手动使用SQL UPDATE语句初始化具有标准值的字段。

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

另一种解决方案可以是指定文件的默认值。

#8

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。现在在构造函数中。希望它能帮助一些人。

#9

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:

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

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?属性对模型中的所有实体。

#10

Also, if you don't know part of code where error occured, you can profile "bad" sql execution using sql profiler integrated to mssql.

此外，如果您不知道发生错误的部分代码，您可以使用集成到mssql的sql profiler来描述“糟糕”的sql执行。

Bad datetime param will displayed something like that :

坏的datetime param会显示如下内容:

#11

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属性，一个DateTime属性在我做SaveChanges之前是空的。

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

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

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; }

#12

If you are using Entity Framework version >= 5 then applying the [DatabaseGenerated(DatabaseGeneratedOption.Computed)] annotation to your DateTime properties of your class will allow the database table's trigger to do its job of entering dates for record creation and record updating without causing your Entity Framework code to gag.

如果您使用的是实体框架版本>= 5，那么将[DatabaseGenerated option.computed]注释应用到类的DateTime属性将允许数据库表的触发器输入创建记录和记录更新的日期，而不会导致实体框架代码gag。

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

[DatabaseGenerated(DatabaseGeneratedOption.Computed))

public DateTime DateCreated { get; set; }

公共DateTime DateCreated {get;设置;}

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

[DatabaseGenerated(DatabaseGeneratedOption.Computed))

public DateTime DateUpdated { get; set; }

公共DateTime DateUpdated {get;设置;}

This is similar to the 6th answer, written by Dongolo Jeno and Edited by Gille Q.

这类似于第6个答案，由杰诺(Dongolo Jeno)撰写，吉尔·Q (Gille Q)编辑。

#13

Try making your property nullable.

尝试使您的属性无效。

    public DateTime? Time{ get; set; }

Worked for me.

为我工作。

#14

If you ahve access to the DB, you can change the DB column type from datetime to datetime2(7) it will still send a datetime object and it will be saved

如果您希望访问DB，您可以将DB列类型从datetime更改为datetime2(7)，它仍然会发送一个datetime对象，并将其保存

#15

The model should have nullable datetime. The earlier suggested method of retrieving the object that has to be modified should be used instead of the ApplyPropertyChanges. In my case I had this method to Save my object:

模型应该具有可空的datetime。应该使用前面建议的检索必须修改的对象的方法，而不是ApplyPropertyChanges。在我的案例中，我有这个方法来保存我的对象:

public ActionResult Save(QCFeedbackViewModel item)

And then in service, I retrieve using:

然后在服务中，我使用:

RETURNED = item.RETURNED.HasValue ? Convert.ToDateTime(item.RETURNED) : (DateTime?)null 

The full code of service is as below:

服务的完整代码如下:

 var add = new QC_LOG_FEEDBACK()
            {

                QCLOG_ID = item.QCLOG_ID,
                PRE_QC_FEEDBACK = item.PRE_QC_FEEDBACK,
                RETURNED = item.RETURNED.HasValue ? Convert.ToDateTime(item.RETURNED) : (DateTime?)null,
                PRE_QC_RETURN = item.PRE_QC_RETURN.HasValue ? Convert.ToDateTime(item.PRE_QC_RETURN) : (DateTime?)null,
                FEEDBACK_APPROVED = item.FEEDBACK_APPROVED,
                QC_COMMENTS = item.QC_COMMENTS,
                FEEDBACK = item.FEEDBACK
            };

            _context.QC_LOG_FEEDBACK.Add(add);
            _context.SaveChanges();

#16

[Solved] In Entity Framework Code First (my case) just changing DateTime to DateTime? solve my problem.

[已解决]在实体框架代码中(以我为例)只是将DateTime更改为DateTime?解决我的问题。

/*from*/ public DateTime SubmitDate { get; set; }
/*to  */ public DateTime? SubmitDate { get; set; }