Conversion of a datetime2 data type to a datetime data type results out-of-range value

Question

I ve got a datatable with 5 columns  where a row is being filled with data then saved to the database via a transaction   While saving  an error is returned      The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value   It implies  as read  that my datatable has a type of DateTime2 and my database a DateTime  that is wrong   The date column is set to a DateTime like this    new DataColumn  myDate   Type GetType  System DateTime     Question  Can this be solved in code or does something have to be changed on a database level

User · Accepted Answer

What kind of dates do you have in the column?

Do all of them fit within the range of the type?

As an aside, the correct way to get a Type object for the DataColumn constructor is the typeof keyword, which is orders of magnitude faster.

Therefore, to create the column, you should write

new DataColumn("myDate", typeof(DateTime))

User · Answer

I ran into this issue on a simple console app project and my quick solution is to convert any possible datetime2 dates to a nullable datetime by running this method   static DateTime  ParseDateTime2 DateTime  date                if  date    null    date ToString       1 1 0001 12 00 00 AM                         return null                    else                       return date                    This is certainly not a completely comprehensive method  but it worked for my needs and maybe it ll help others

User · Answer

Sometimes EF does not know that is dealing with a computed column or a trigger  By design  those operations will set a value outside of EF after an insert    The fix is to specify Computed in EF s edmx for that column in the StoreGeneratedPattern property    For me it was when the column had a trigger which inserted the current date and time  see below in the third section     Steps To Resolve  In Visual Studio open the Model Browser page then Model then Entity Types -  then   Select the entity and the date time property Select  StoreGeneratedPattern  Set to Computed       For this situation other answers are workarounds  for the purpose of the column is to have a time date specified when the record was created  and that is SQL s job to execute a trigger to add the correct time  Such as this SQL trigger   DEFAULT  GETDATE    FOR  DateCreated

User · Answer

Add the below mentioned attribute on the property in your model class   Attribute    DatabaseGenerated DatabaseGeneratedOption Computed   Reference   System ComponentModel DataAnnotations Schema   Initially I forgot to add this attribute  So in my database the constraint was created like  ALTER TABLE  dbo   TableName  ADD DEFAULT  getdate    FOR  ColumnName    and I added this attribute and updated my db then it got changed into  ALTER TABLE  dbo   TableName  ADD CONSTRAINT  DF dbo TableName ColumnName  DEFAULT  getdate    FOR  ColumnName

User · Answer

Check the req format in DB  eg my DB have Default value or Binding    1   1    1900    System DateTime MyDate   new System DateTime  1900  1  1

User · Answer

In my SQL Server 2008 database  I had a DateTime column flagged as not nullable  but with a GetDate   function as its default value  When inserting new object using EF4  I got this error because I wasn t passing a DateTime property on my object explicitly  I expected the SQL function to handle the date for me but it did not  My solution was to send the date value from code instead of relying on the database to generate it    obj DateProperty   DateTime now     C

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 Server 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

I m aware of this problem and you all should be too   https   en wikipedia org wiki Year 2038 problem  In SQL a new field type was created to avoid this problem  datetime2    This  Date  field type has the same range values as a DateTime  Net class  It will solve all your problems  so I think the best way of solving it is changing your database column type  it won t affect your table data

User · Answer

I found this post trying to figure why I kept getting the following error which is explained by the other answers   The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value   Use a nullable DateTime object      public DateTime  PurchaseDate   get  set     If you are using entity framework Set the nullable property in the edmx file to True

User · Answer

Check out the following two  1  This field has no NULL value  For example    public DateTime MyDate   get  set      Replace to   public DateTime MyDate   get  set    DateTime Now    2  New the database again  For example   db new MyDb

User · Answer

The easiest thing would be to change your database to use datetime2 instead of datetime  The compatibility works nicely  and you won t get your errors   You ll still want to do a bunch of testing     The error is probably because you re trying to set a date to year 0 or something - but it all depends on where you have control to change stuff

User · Answer

Problem with inherited datetime attribute  This error message is often showed when a non-nullable date field has value null at insert update time  One cause can be inheritance   If your date is inherit from a base-class and you don t make a mapping EF will not read it s value   For more information  https   weblogs asp net manavi inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-3-table-per-concrete-type-tpc-and-choosing-strategy-guidelines

User · Answer

Both the DATETIME and DATETIME2 map to System DateTime in  NET - you cannot really do a  conversion   since it s really the same  NET type   See the MSDN doc page   http   msdn microsoft com en-us library bb675168 aspx  There are two different values for the  SqlDbType  for these two - can you specify those in your DataColumn definition   BUT  on SQL Server  the date range supported is quite different   DATETIME supports 1753 1 1 to  eternity   9999 12 31   while DATETIME2 supports 0001 1 1 through eternity   So what you really need to do is check for the year of the date - if it s before 1753  you need to change it to something AFTER 1753 in order for the DATETIME column in SQL Server to handle it   Marc

User · Answer

For me I have had a Devexpress DateEdit component  which was binded to nullable datetime MSSQL column thru the Nullable model property  All I had to do was setting AllowNullInput   True on DateEdit  Having it  quot Default quot  caused that the date 1 1 0001 appeared - ONLY BEFORE leaving the DateEdit - and then I got this conversion error message because of subsequent instructions mentioned above

User · Answer

If we dont pass a date time to date time field the default date  1 1 0001 12 00 00 AM  will be passed   But this date is not compatible with entity frame work so it will throw  conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value  Just default DateTime now to the date field if you are not passing any date    movie DateAdded   System DateTime Now

User · Answer

This can happen if you do not assign a value to a DateTime field when the field does not accept NULL values    That fixed it for me

User · Answer

for me it was because the datetime was       01 01 0001 00 00 00   in this case you want to assign null to you EF DateTime Object    using my FirstYearRegistered code as an example  DateTime FirstYearRegistered   Convert ToDateTime Collection  FirstYearRegistered     if  FirstYearRegistered    DateTime MinValue        vehicleData DateFirstReg   FirstYearRegistered

User · Answer

The Entity Framework 4 works with the datetime2 data type so in db the corresponding field must be datetime2 for SQL Server 2008    To achive the solution there are two ways    To use the datetime data type in Entity Framwork 4 you have to switch the ProviderManifestToken in the edmx-file to  2005   If you set corresponding field as Allow Null  it converts it to NULLABLE  so then EF automatically uses date objects as datetime

User · Answer

Adding this code to a class in ASP NET worked fort me   protected override void OnModelCreating DbModelBuilder modelBuilder        modelBuilder Properties lt DateTime gt    Configure c   gt  c HasColumnType  datetime2

User · Answer

I ran into this and added the following to my datetime property     Column TypeName    datetime2     public DateTime  NullableDateTimePropUtc   get  set

User · Answer

Sometimes it works fine on development machines and not in servers  In my case I had to put     lt globalization uiCulture  es  culture  es-CO    gt    In the web config file     The timezone in the machine  Server  was right  to the CO locale  but the web app did not  This setting done and it worked fine again    Off course  all dates had value     D

User · Answer

you will have date column which was set to lesathan the min value of allowed dattime like 1 1 1001    to overcome this issue you can set the proper datetime value to ur property adn also set another magical property like IsSpecified true

User · Answer

I saw this error when I wanted to edit a page usnig ASP Net MVC  I had no problem while Creating but Updating Database made my DateCreated property out of range    When you don t want your DateTime Property be Nullable and do not want to check if its value is in the sql DateTime range  and  Html HiddenFor doesn t help    simply add a static DateTime field inside related class  Controller  and give it the value when GET is operating then use it when POST is doing it s job   public class PagesController   Controller       static DateTime dateTimeField      UnitOfWork db   new UnitOfWork            GET      public ActionResult Edit int  id                Page page   db pageRepository GetById id           dateTimeField   page DateCreated          return View page                 POST        HttpPost       ValidateAntiForgeryToken      public ActionResult Edit Page page                page DateCreated   dateTimeField          db pageRepository Update page           db Save            return RedirectToAction  Index

User · Answer

As andyuk has already pointed-out  this can happen when a NULL value is assigned to a non nullable DateTime field  Consider changing DateTime to DateTime  or Nullable lt DateTime   Bear in mind that  in case you are using a Dependency Property  should also make sure that your dependency property s type is also a nullable DateTime type   Below is a real life example of an incomplete DateTime to DateTime  type adjustment that raises the odd behaviour

User · Answer

Created a base class based on  sky-dev implementation  So this can be easily applied to multiple contexts  and entities   public abstract class BaseDbContext lt TEntity gt    DbContext where TEntity   class       public BaseDbContext string connectionString            base connectionString                  public override int SaveChanges                  UpdateDates            return base SaveChanges               private void UpdateDates                 foreach  var change in ChangeTracker Entries lt TEntity 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                                                                            Usage   public class MyContext  BaseDbContext lt MyEntities gt              lt summary gt          Initializes a new instance of the  lt see cref  MyContext   gt  class           lt  summary gt      public MyContext             base  name MyConnectionString                        lt summary gt          Initializes a new instance of the  lt see cref  MyContext   gt  class           lt  summary gt           lt param name  connectionString  gt The connection string  lt  param gt      public MyContext string connectionString            base connectionString                      DBcontext class body here  methods  overrides  etc

User · Answer

In my case we were casting a Date to a Datetime and we got this error  What happens is that Date has a  more programmer oriented  minimum of 01 01 0001  while Datetime is stuck at 1753  Combine that with a data collection error on our part  and you get your exception

[c#] Conversion of a datetime2 data type to a datetime data type results out-of-range value

Examples related to c#

Examples related to sql-server

Examples related to entity-framework

Examples related to datetime

Examples related to orm