how to fix "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value." in C# ASP.net MVC 5?

Question

First of all I like to state that I am new to c# ASP.NET. I am using ASP.NET MVC 5 and Entity framework with a code first approach.

When I try to insert a new record into the database I get this error

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

Here is what I did so far.

First I created the following 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 ScripterEngine.Models
{
    [Table("campaign_calllogs")]

    public class Calllog
    {

        [Key]
        public int id { get; set; }
        public int campaign_id { get; set; }
        public int callid { get; set; }
        public string dialer_campaign_id { get; set; }
        public string dialing_mode { get; set; }
        public int attempts { get; set; }
        public string phone { get; set; }
        public int i3_identity { get; set; }
        public string wrapupcode { get; set; }
        public string call_notes { get; set; }
        public string completed_by { get; set; }
        public string callidkey { get; set; }
        public string direction { get; set; }
        public string local_id { get; set; }
        public string local_location { get; set; }
        public string local_name { get; set; }

        public string remote_id { get; set; }
        public string remote_location { get; set; }
        public string remote_name { get; set; }

        public DateTime? connected_at { get; set; }
        public DateTime? disconnected_at { get; set; }
        public DateTime? call_placed_at { get; set; }

        public DateTime? assigned_at { get; set; }
        public DateTime? completed_at { get; set; }

        public DateTime? creation_time { get; set; }
        public DateTime created_at { get; set; }
        public DateTime? updatd_at { get; set; }

    }
}

Then I am trying to add a new record from a controller.

Here is what I have done in my add action in the controller

public string Add()
{
    dynamic respond = new ExpandoObject();
    respond.logged = false;

    int campaignId = Parser.intVal(Request["campaign_id"]);
    int callid = Parser.intVal(Request["callid"]);
    int i3Identity = Parser.intVal(Request["i3_identity"]);
    int attempts = Parser.intVal(Request["attempts"]);
    DateTime? assigneAt = Parser.dateTimeVal(Request["assigned_at"]);
    DateTime? completedAt = Parser.dateTimeVal(Request["completed_at"]);
    DateTime? creationTime = Parser.dateTimeVal(Request["creation_time"]);
    DateTime? connectedAt = Parser.dateTimeVal(Request["connected_at"]);
    DateTime? disconnectedAt = Parser.dateTimeVal(Request["disconnected_at"]);
    DateTime? callPlacedAt = Parser.dateTimeVal(Request["call_placed_at"]);

    if (campaignId > 0 && callid > 0 && i3Identity > 0 && ModelState.IsValid)
    {

        Calllog log = new Calllog();

        log.campaign_id = campaignId;
        log.callid = callid;
        log.i3_identity = i3Identity;
        log.attempts = attempts;
        log.assigned_at = assigneAt;
        log.completed_at = completedAt;
        log.creation_time = creationTime;
        log.connected_at = connectedAt;
        log.disconnected_at = disconnectedAt;
        log.call_placed_at = callPlacedAt;

        log.phone = Request["phone"];
        log.wrapupcode = Request["wrapupcode"];
        log.callidkey = Request["callidkey"];
        log.direction = Request["direction"];

        log.completed_by = Request["completed_by"];
        log.dialer_campaign_id = Request["dialer_campaign_id"];
        log.call_notes = Request["call_notes"];
        log.completed_by = Request["completed_by"];

        log.dialing_mode = Request["dialing_mode"];
        log.local_id = Request["local_id"];
        log.local_location = Request["loacl_location"];
        log.local_name = Request["local_name"];

        log.remote_id = Request["remote_id"];
        log.remote_location = Request["remote_location"];
        log.remote_name = Request["remote_name"];

        db.Calllog.Add(log);
        db.SaveChanges();
        respond.logged = true;

    }

    return JsonHelpers.ToJson(respond);
}

The exception points at this line db.SaveChanges();

First of all what is the different between datetime and datetime2? is one nullable and the other not?

What could be causing this error?

http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime this should be helpfull — Dmytro, Feb 04 '16 at 16:21
@DmytroTsiniavskyi Thank you for the the link that defiantly helps understanding the different between datetime and datetime2. How would I solve this problem? How can I tell the MVC to tract the DateTime types as datetime2 rather than datetime? — Junior, Feb 04 '16 at 16:26

score 1 · Accepted Answer · answered Feb 04 '16 at 16:58

1

Most often this error is thrown because somewhere you are giving a datetime of null or '0001-01-01'.

Go into the debugger and see what values you are getting for your datetimes.

99% of the time this is the problem as Datetime only goes back to a certain date whereas datetime2 includes the zero-one date.

answered Feb 04 '16 at 16:58

rory

1,490
3
22
50

I expect some values to be null, but this is why I initialized my datatime to be nullable by adding `?` to DateTime. Is this this how you would make a column nullable and then pass a null value to it? – Junior Feb 04 '16 at 17:10
The created_at column is not nullable which is why I was getting the error. after I added log.created_at = DateTime.New in my controller it worked. – Junior Feb 04 '16 at 17:12
@MikeA - glad I could help, I lost a good few hours to this issue myself. – rory Feb 08 '16 at 13:12

how to fix "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value." in C# ASP.net MVC 5?

1 Answers1