CODEWITHSUNDEEP
c#entity-framework
Cameron
Cameron

Reputation: 28793

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?

Upvotes: 198

Views: 301540

Answers (24)

Nigrimmist
Nigrimmist

Reputation: 12368

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 :

bad param

Upvotes: 2

Jackdon Wang
Jackdon Wang

Reputation: 407

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

Upvotes: 0

syter
syter

Reputation: 135

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

Upvotes: 0

CenGokhan
CenGokhan

Reputation: 1

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

Upvotes: 0

marc
marc

Reputation: 1

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

Upvotes: 0

Badr Bellaj
Badr Bellaj

Reputation: 12841

You have to enable null value for your date variable :

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

Upvotes: 2

Aduwu Joseph
Aduwu Joseph

Reputation: 13

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.

Upvotes: 1

Ansar Nisar
Ansar Nisar

Reputation: 21

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.

Upvotes: 0

pa1k
pa1k

Reputation: 181

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!

Upvotes: 0

Andrew Orsich
Andrew Orsich

Reputation: 53685

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.

Upvotes: 47

Jim Kay
Jim Kay

Reputation: 43

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.

Upvotes: 3

Amir Astaneh
Amir Astaneh

Reputation: 2206

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

Upvotes: 3

anu
anu

Reputation: 315

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

Upvotes: 0

Willy David Jr
Willy David Jr

Reputation: 9131

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

Upvotes: 3

Sergiu Mindras
Sergiu Mindras

Reputation: 195

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

Upvotes: 0

typhon04
typhon04

Reputation: 2624

Try making your property nullable.

    public DateTime? Time{ get; set; }

Worked for me.

Upvotes: 0

Dongolo Jeno
Dongolo Jeno

Reputation: 444

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

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

Upvotes: 12

Ogglas
Ogglas

Reputation: 69968

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.

Upvotes: 3

Alexander Khomenko
Alexander Khomenko

Reputation: 597

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.

Upvotes: 5

StinkyCat
StinkyCat

Reputation: 1276

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.

Upvotes: 3

Sanjay Kumar Madhva
Sanjay Kumar Madhva

Reputation: 1319

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.

Upvotes: 131

sky-dev
sky-dev

Reputation: 6258

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

Upvotes: 17

Patrick
Patrick

Reputation: 89

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

Upvotes: 8

Jacob
Jacob

Reputation: 78840

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

Upvotes: 181

Related Questions