Conversion of a datetime2 data type to a datetime data error

Question

I am new to MVC and I am getting the following error when I try to save to the Database

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

Model:

    [Required]
    [Display(Name = "Date")]
    [DataType(DataType.Date)]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:dd/MM/yyyy}")]
    public DateTime Date { get; set; }

Controller:

   [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create(ReorderContactLenses reorderContactLenses)
    {
        // If Model State is Valie
        if (ModelState.IsValid)
        {
            // Generateds an OrderId 
            reorderContactLenses.OrderId = Guid.NewGuid();

            // Checks if user is logged in 
            if (User.Identity.IsAuthenticated)
            {
                // Gets the UserId
                string userId = User.Identity.GetUserId();

                // Finds PatientId where the Patient.UserId matchs that of the logged in User and assigns the PatientId to reorderContactLenses.PatientId
                reorderContactLenses.PatientId = (from d in db.Patients
                                                  where d.UserId == userId
                                                  select d.PatientId).Single();
            }

            // Sets the reorderContactLense Date to todays Date
            reorderContactLenses.Date = DateTime.Now.Date;
            //Adds reorderContactLenses to the database
            db.ReorderContactLenses.Add(reorderContactLenses);

            // Initilises and Order Status
            OrderStatus orderStatus = new OrderStatus();
            // Generates an Id for the Order Status
            orderStatus.OrderStatusId = new Guid();
            // Assigns the reorderContactLenses OrderId to Order Status OrderId
            orderStatus.OrderId = reorderContactLenses.OrderId;
            // Sets the Ordered Bool to false
            orderStatus.Ordered = false;
            // Adds the Order Status to the Database
            db.OrderStatus.Add(orderStatus);
            //Saves the changes
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        ConfigureCreateView(reorderContactLenses);
        return View(reorderContactLenses);
    }

in SQL Server, datetime cannot be null (unless you mark it as such) and cannot be before January 1, 1753. You are violating one of these constraints. You'll need to set your breakpoints and step through the code to figure out what is wrong. — user1666620

user1666620 user1666620 · Accepted Answer · 2015-09-08T14:22:16

In SQL Server, datetime cannot be null (unless you mark it as such) and cannot be before January 1, 1753. You are violating one of these constraints. I don't see you setting a DateTime property in your OrderStatus object. I suggest that is your problem.

You could set a default date in your database for that particular column using something like the following:

ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn

However I don't recommend this as you are hiding logic away in your database when, in my opinion, it should be declared explicitly in the application.

Conversion of a datetime2 data type to a datetime data error

1 Answers