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    lt   using  Html BeginForm       gt        lt fieldset gt           lt legend gt Fields lt  legend gt           lt p gt               lt label for  headline  gt Headline lt  label gt               lt    Html TextBox  headline     gt           lt  p gt           lt p gt               lt label for  story  gt Story  lt span gt   HTML Allowed   lt  span gt  lt  label gt               lt    Html TextArea  story     gt           lt  p gt           lt p gt               lt label for  image  gt Image URL lt  label gt               lt    Html TextBox  image     gt           lt  p gt           lt p gt               lt input type  submit  value  Post    gt           lt  p gt       lt  fieldset gt    lt       gt    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

User · Answer

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.

User · Answer

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 lt DateTime gt    Configure c   gt  c HasColumnType  datetime2       That will get all the DateTime and DateTime  properties on all the entities in your model

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try making your property nullable       public DateTime  Time  get  set      Worked for me

User · Answer

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  quot Index quot     listele sayfasina y  nlendir

User · Answer

You can also fix this problem by adding to model  Entity Framework version    5    DatabaseGenerated DatabaseGeneratedOption Computed   public DateTime CreationDate   get  set

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 displayed something like that

User · Answer

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 lt MyEntity gt    Property e   gt  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 lt MyEntityBaseClass gt                           var values   change CurrentValues              foreach  var name in values PropertyNames                                var value   values name                   if  value is DateTime                                        var date    DateTime value                      if  date  lt  SqlDateTime MinValue Value                                                values name    SqlDateTime MinValue Value                                            else if  date  gt  SqlDateTime MaxValue Value                                                values name    SqlDateTime MaxValue Value

User · Answer

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   gt  a storyId    ArticleToEdit storyId   story headline   ArticleToEdit headline  story story   ArticleToEdit story  story image   ArticleToEdit image  story modifiedDate   DateTime Now   db SubmitChanges

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  model DateCreated    Adding this to your edit view  you ll never have that error I assure you

User · Answer

You have to enable null value for your date variable     public Nullable lt DateTime gt  MyDate  get  set

User · Answer

change   quot CreateDate quot    quot 0001-01-01 00 00 00 quot  to   quot CreateDate quot    quot 2020-12-19 00 00 00 quot   CreateDate type is public DateTime CreateDate error json     quot keyValue quot   1   quot entity quot      quot TodoId quot   1   quot SysId quot    quot 3730e2b8-8d65-457a-bd50-041ce9705dc6 quot    quot AllowApproval quot   false   quot ApprovalUrl quot   null   quot ApprovalContent quot   null   quot IsRead quot   true   quot ExpireTime quot    quot 2020-12-19 00 00 00 quot    quot CreateDate quot    quot 0001-01-01 00 00 00 quot    quot CreateBy quot   null   quot ModifyDate quot    quot 2020-12-18 9 42 10 quot    quot ModifyBy quot   null   quot UserId quot    quot f5250229-c6d1-4210-aed9-1c0287ab1ce3 quot    quot MessageUrl quot    quot https   bing com quot      correct json     quot keyValue quot   1   quot entity quot      quot TodoId quot   1   quot SysId quot    quot 3730e2b8-8d65-457a-bd50-041ce9705dc6 quot    quot AllowApproval quot   false   quot ApprovalUrl quot   null   quot ApprovalContent quot   null   quot IsRead quot   true   quot ExpireTime quot    quot 2020-12-19 00 00 00 quot    quot CreateDate quot    quot 2020-12-19 00 00 00 quot    quot CreateBy quot   null   quot ModifyDate quot    quot 2020-12-18 9 42 10 quot    quot ModifyBy quot   null   quot UserId quot    quot f5250229-c6d1-4210-aed9-1c0287ab1ce3 quot    quot MessageUrl quot    quot https   bing com quot

User · Answer

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

[asp.net-mvc] The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

Examples related to asp.net-mvc