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

Question

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

My application recently started to show this error, it is quite strange, because it worked earlier. I didn't change anything connected with DateTime in my "Word" Model. It started to happen when I added new Models to my project.

Server Error shows up when I try to Edit data. Creating and Deleting works fine.

Controller:

[HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Edit([Bind(Include = "ID,UsersLanguage,OtherLanguage,Notes")] Word word, int idOfCollection)
    {
        if (ModelState.IsValid)
        {
            db.Entry(word).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Index", new { idOfCollection = idOfCollection });
        }
        return View(word);
    }

Model:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace WebLanguageTeacher.Models.MyDatabase
{
    public class Word
    {
        public int ID { get; set; }
        [MinLength(2, ErrorMessage = "Wydaje mi się, że słowo powinno mieć przynajmniej 2 litery ;)")] 
        [DisplayName("Język Użytkownika")]
        [Required]
        public string UsersLanguage { get; set; }
        [MinLength(2, ErrorMessage = "Wydaje mi się, że słowo powinno mieć przynajmniej 2 litery ;)")] 
        [DisplayName("Inny język")]
        [Required]
        public string OtherLanguage { get; set; }
        [DisplayName("Notatki")]
        public string Notes { get; set; }
        [DisplayName("Ostatnia powtórka")]
        public DateTime LastReviewed { get; set; }
        [DisplayName("Następna powtórka")]
        public DateTime NextReview { get; set; }
        [DefaultValue(0)]
        [DisplayName("Przerwa między powtórkami")]
        public int ReviewInterval { get; set; } /*W miejsce Difficulty*/

        [DisplayName("Nazwa właściciela")]
        public string OwnerName { get; set; }
        public virtual Collection Collection { get; set; }

        [NotMapped]
        public bool ModifyReview { get; set; } /* Klient przesyła tylko za ile dni będzie następna powtórka, serwer sam generuje datę*/

        public Word(){
            ModifyReview = false;
        }
    }
}

What's wrong? I don't create any DateTime2 variables, so why my app tries to convert DateTime2 to DateTime?

I use ASP.net MVC with EntityFramework.

possible duplicate of [c# conversion of a datetime2 data type to a datetime data type](http://stackoverflow.com/questions/1331779/c-sharp-conversion-of-a-datetime2-data-type-to-a-datetime-data-type) — Arion, Jan 21 '15 at 08:02
What are the actual values of `LastReviewed` and `NextReview` before calling `SaveChanges()`? — haim770, Jan 21 '15 at 08:04
Perhaps one or more of your `DateTime` values has a value that is out of the range of an SQL DATETIME variable (for example: if it has its default value of `DateTime.MinValue`). Try stepping through with a debugger and examining the values of LastReviewed / NextReview. — Joe, Mar 23 '17 at 10:45

score 2 · Answer 1 · answered Jan 21 '15 at 08:25

The DateTime object defaults to DateTime.MinValue when it's value isn't explicitly set.

So you have a DateTime object in your model that isn't being set and is defaulting to this, which as noted above, is out of the range of a DateTime dbtype, so EntityFramework converts it to a DateTime2 dbtype, which then causes the cast error in your database.

To solve it, check out all your DateTime objects in your model, and make sure they are set anything other than DateTime.MinValue. If you don't want to set the value to anything, then make that field nullable in both your db and your model, and things will work

NB, setting a default on the db column doesn't solve this, the conversion happens too early, you must explicitly set the value

score 1 · Answer 2 · answered Jan 21 '15 at 08:06

1

DateTime range: January 1, 1753, - December 31, 9999
DateTime2 range: 0001-01-01 - 9999-12-31

Use below code in order to avoid conflict.

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
  modelBuilder.Entity<Word>().Property(o => o.NextReview).HasColumnType("datetime2");
 }

answered Jan 21 '15 at 08:06

Ilya Sulimanov

7,636
6
47
68

Why I have to do this that strange way? Can't I just declare it somehow while creating variable? Like "public DateTime2 LastReviewed { get; set; }" – Piotrek Jan 21 '15 at 16:42
In you DBContext class. https://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext.onmodelcreating%28v=vs.113%29.aspx – Ilya Sulimanov Jan 22 '15 at 07:03

score 0 · Answer 3 · answered Jan 21 '15 at 08:13

0

Make datetime nullable you need to force them explicitly. Like if property "LastReviewed" is nullable you should declare it like below.

[DisplayName("Ostatnia powtórka")]
public DateTime? LastReviewed { get; set; }

Same with other datetime variables if they are nullable

answered Jan 21 '15 at 08:13

azhar_SE_nextbridge

304
3
19

But I don't want to make it nullable. I need it to be set, it is one of most important variables here. – Piotrek Jan 21 '15 at 16:30
By default it assign min value to that datetime object if you want to assign it value then in case you assign value but it is not mapping correctly that's why this error happened... – azhar_SE_nextbridge Jan 22 '15 at 10:07

score 0 · Answer 4 · answered Feb 01 '15 at 16:17

You need to preserve the old value for the datetime fields (and all other fields you do not want to modify) if you do not like them to be changed. To do so retrieve old entity from DB and just change the fields that are updated in the edit form. For example:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "ID,UsersLanguage,OtherLanguage,Notes")] Word word, int idOfCollection)
{
    if (ModelState.IsValid)
    {
        var dbWord = db.Words.Find(word.ID);
        dbWord.UsersLanguage = word.UsersLanguage;
        dbWord.OtherLanguage = word.OtherLanguage;
        dbWord.Notes = word.Notes;

        db.SaveChanges(); // in this case dbWord is saved so datetime fields remains intact
        return RedirectToAction("Index", new { idOfCollection = idOfCollection });
    }
    return View(word);
}

score 0 · Answer 5 · answered Mar 23 '17 at 10:39

0

Make you property nullable by adding a question mark after DateTime.

    public DateTime? SentOn { get; set; }

answered Mar 23 '17 at 10:39

typhon04

2,350
25
22

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

5 Answers5

Linked