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

I have the following code in my 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?

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.

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.

Update:

Here's an untested sample of just updating some fields of your object (this is assuming you're using 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();

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/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.

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

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

Msdn

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

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");).

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

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

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

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

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 Statement manually for initializing the field with a standard value.

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

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.

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:

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

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

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

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

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

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

Bad datetime param will be displayed something like here :

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

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

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.

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

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

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

You have to enable null value for your date variable :

 public Nullable<DateTime> MyDate{ get; set; }

This problem usually occurs when you are trying to update an entity. For example you have an entity that contains a field called DateCreated which is [Required] and when you insert record, no error is returned but when you want to Update that particular entity, you the get the

>datetime2 conversion out of range error.

Now here is the solution:

On your edit view, i.e. edit.cshtml for MVC users all you need to do is add a hidden form field for your DateCreated just below the hidden field for the primary key of the edit data.

Example:

@Html.HiddenFor(model => model.DateCreated)

Adding this to your edit view, you'll never have that error I assure you.

Try making your property nullable.

    public DateTime? Time{ get; set; }

Worked for me.

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

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:

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

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

This error occurred when due to NOT assigning any value against a NOT NULL date column in SQL DB using EF and was resolved by assigning the same.

Hope this helps!

Got this problem when created my classes from Database First approach. Solved in using simply Convert.DateTime(dateCausingProblem) In fact, always try to convert values before passing, It saves you from unexpected values.

you have to match the input format of your date field to the required entity format which is yyyy/mm/dd

I think the most logical answer in this regard is to set the system clock to the relevant feature.

 [HttpPost]
        public ActionResult Yeni(tblKategori kategori)
        {
            kategori.CREATEDDATE = DateTime.Now;
            var ctx = new MvcDbStokEntities();
            ctx.tblKategori.Add(kategori);
            ctx.SaveChanges();
            return RedirectToAction("Index");//listele sayfasına yönlendir.
        }

It is likely something else, but for the future readers, check your date time format. i had a 14th month

change "CreateDate": "0001-01-01 00:00:00" to "CreateDate": "2020-12-19 00:00:00",

CreateDate type is public DateTime CreateDate

error json:

{ "keyValue": 1, "entity": { "TodoId": 1, "SysId": "3730e2b8-8d65-457a-bd50-041ce9705dc6", "AllowApproval": false, "ApprovalUrl": null, "ApprovalContent": null, "IsRead": true, "ExpireTime": "2020-12-19 00:00:00", "CreateDate": "0001-01-01 00:00:00", "CreateBy": null, "ModifyDate": "2020-12-18 9:42:10", "ModifyBy": null, "UserId": "f5250229-c6d1-4210-aed9-1c0287ab1ce3", "MessageUrl": "https://bing.com" } }

correct json:

{ "keyValue": 1, "entity": { "TodoId": 1, "SysId": "3730e2b8-8d65-457a-bd50-041ce9705dc6", "AllowApproval": false, "ApprovalUrl": null, "ApprovalContent": null, "IsRead": true, "ExpireTime": "2020-12-19 00:00:00", "CreateDate": "2020-12-19 00:00:00", "CreateBy": null, "ModifyDate": "2020-12-18 9:42:10", "ModifyBy": null, "UserId": "f5250229-c6d1-4210-aed9-1c0287ab1ce3", "MessageUrl": "https://bing.com" } }