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

Question

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?

`modelBuilder.Entity().Property(d => d.CurrentTime).HasColumnType("datetime2");` — Anthony Nichols, Sep 30 '15 at 19:27

Jacob · Accepted Answer · 2011-01-05T21:51:50.847

180

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

edited Jan 05 '11 at 21:51

answered Jan 05 '11 at 21:34

Jacob

77,566
24
149
228

Very helpful :) I was wondering why the date was being altered when I hadn't specified it. Can you help change the Edit method then so it no longer uses the ApplyPropertyChanges? As I'm new to ASP.NET and don't fully understand it all at the moment. Thanks pal. – Cameron Jan 05 '11 at 21:37
What if I wanted to set the date to the current date ie. the datetime that the article has been updated? As that's probably gonna be the best option and I presume it would work with the ApplyPropertyChanges I have in play. – Cameron Jan 05 '11 at 21:50
See my edit (it assumes that `modifiedDate` is the name of your property) – Jacob Jan 05 '11 at 21:52
SubmitChanges doesn't work in my App :/ is it possible to add the `story.modifiedDate = DateTime.Now;` bit to my current code? Thanks – Cameron Jan 05 '11 at 21:55
SaveChanges does work with your code though :) but if say I wanted to keep my original code in play how could I use the `story.modifiedDate = DateTime.Now;` with the original ApplyPropertyChanges – Cameron Jan 05 '11 at 21:57
If you want to still use `ApplyPropertyChanges` and there are no other properties besides the modified date that could get overwritten, you could just set the modified date of `ArticleToEdit` to `DateTime.Now` before your call to `ApplyPropertyChanges`. – Jacob Jan 05 '11 at 21:59
Figured it out :) `ArticleToEdit.posted = DateTime.Now;` Thanks for all your help. – Cameron Jan 05 '11 at 22:04
Just had the same issued with code first, made my datetime field nullable. thanks for the hint. – JTew Oct 13 '11 at 11:29
@Jacob : I have used `IsModified` and set it `false` and the modified date is written the same way you have mentioned. i have another class where my same logic for edit works. but not in one. I have checked the DB and the data type of properties `CreatedOn` and `ModifiedOn` are `datetime`. any suggestions for what causing me the error? – Vini Oct 15 '15 at 05:54
1

this error occurs when we set public DateTime CreatedDate instead of public DateTime? CreatedDate, because DateTime can not be null, so why its give out of range error. it may helpful, but it resolved my problem. – adnan May 23 '16 at 06:55

score 130 · Answer 2 · edited Dec 13 '13 at 00:48

130

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.

edited Dec 13 '13 at 00:48

Kirk Woll

76,112
22
180
195

answered Jul 12 '12 at 09:27

Sanjay Kumar Madhva

1,309
1
8
2

6

This is happens when the datetime field in database is optional and value is not set. Therefor this is not common people error this is one of the EF architectural issues. – GSoft Consulting Sep 13 '15 at 00:11
1

I agree EF should see the type in DB and adjust accordingly or throw or at least throw a more precise error. On the upside, I like your answer since you provided 2 options instead of the usual one - initialize the filed with min value. – DanteTheSmith Jan 24 '18 at 09:39
This solved my issue. In particular, I was trying to pass just the year as opposed to a valid datetime (ex: 01-05-2021). – David Tunnell Jun 09 '21 at 16:08

score 47 · Answer 3 · edited Sep 11 '18 at 12:39

47

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.

edited Sep 11 '18 at 12:39

TuralAsgar

1,275
2
13
26

answered Jan 05 '11 at 21:20

Andrew Orsich

52,935
16
139
134

What do I put in the Controller though? Debugging just brings up that error I have posted above. Thanks. – Cameron Jan 05 '11 at 21:22
seems error here -> _db.SaveChanges(); You can set breakpoint before this line... – Andrew Orsich Jan 05 '11 at 21:28
Yeah did do. Then it says their is an error on the SaveChanges so I look at the InnerException and it says it's because of the error posted so that's the error! – Cameron Jan 05 '11 at 21:32
Did you checked DateTime value before _db.SaveChanges(); was called? It shoud be great than '1753/1/1'. – Andrew Orsich Jan 05 '11 at 21:36
The originalArticle date value is {18/10/2009 00:00:00} and the ArticleToEdit date value is {01/01/0001 00:00:00} so it seems it's trying to change the date to 1st of everything which isn't want I want but doesn't explain why it is throwing the error as the format is the same right? – Cameron Jan 05 '11 at 21:44
As @Jacob said ApplyPropertyChanges applies changes to all properties of the object and try to save {01/01/0001 00:00:00} into database(this cause error). So use example posted by @Jacob. – Andrew Orsich Jan 05 '11 at 21:54
this error occurs when we set public DateTime CreatedDate instead of public DateTime? CreatedDate, because DateTime can not be null, so why its give out of range error. it may helpful, but it resolved my problem. – adnan May 23 '16 at 06:54

score 17 · Answer 4 · answered Jul 02 '12 at 16:08

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

score 12 · Answer 5 · edited Jan 30 '17 at 11:54

12

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

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

edited Jan 30 '17 at 11:54

Dipiks

3,818
2
23
39

answered Jan 30 '17 at 10:30

Dongolo Jeno

414
6
8

score 8 · Answer 6 · answered Jan 26 '11 at 15:33

8

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

answered Jan 26 '11 at 15:33

Patrick

89
1

score 5 · Answer 7 · answered Jan 28 '16 at 12:23

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.

score 3 · Answer 8 · answered Jul 11 '14 at 19:47

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.

score 3 · Answer 9 · answered Aug 29 '16 at 10:38

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.

score 3 · Answer 10 · answered Jul 16 '17 at 04:29

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

score 3 · Answer 11 · answered Apr 17 '18 at 19:57

3

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

answered Apr 17 '18 at 19:57

Amir Astaneh

2,152
1
20
20

Which is the same as Badr Bellaj above, but bravo for getting the solution down to one character. It also works if your not using code-first, but in thats the case its worth checking your DB column allows nulls. – andrew pate Mar 10 '21 at 20:28

Nigrimmist · Answer 12 · 2021-05-05T07:38:57.250

2

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

edited May 05 '21 at 07:38

answered May 25 '15 at 11:13

Nigrimmist

10,289
4
52
53

score 2 · Answer 13 · edited Jun 27 '18 at 11:25

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.

score 2 · Answer 14 · answered Mar 29 '19 at 09:15

2

You have to enable null value for your date variable :

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

answered Mar 29 '19 at 09:15

Badr Bellaj

11,560
2
43
44

score 1 · Answer 15 · edited Jan 25 '19 at 23:50

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.

score 0 · Answer 16 · answered Mar 23 '17 at 10:34

0

Try making your property nullable.

    public DateTime? Time{ get; set; }

Worked for me.

answered Mar 23 '17 at 10:34

typhon04

2,350
25
22

score 0 · Answer 17 · answered Apr 12 '17 at 13:50

0

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

answered Apr 12 '17 at 13:50

Sergiu Mindras

194
1
17

score 0 · Answer 18 · answered Mar 06 '18 at 21:43

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

score 0 · Answer 19 · answered Dec 17 '18 at 17:20

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!

score 0 · Answer 20 · answered Dec 29 '18 at 08:40

0

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.

answered Dec 29 '18 at 08:40

Ansar Nisar

21
2
5

score 0 · Answer 21 · answered Oct 27 '19 at 09:04

0

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

answered Oct 27 '19 at 09:04

marc

1
1

1

There are other answers that provide the OP's question, and they were posted some time ago. When posting an answer [see: How do I write a good answer?](https://stackoverflow.com/help/how-to-answer), please make sure you add either a new solution, or a substantially better explanation, especially when answering older questions. – help-info.de Oct 27 '19 at 09:29

score 0 · Answer 22 · edited Aug 08 '20 at 17:26

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

score 0 · Answer 23 · answered Aug 19 '20 at 14:22

0

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

answered Aug 19 '20 at 14:22

syter

135
2
10

score 0 · Answer 24 · answered Dec 18 '20 at 02:14

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

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

24 Answers24

Linked