'datetime2' error when using entity framework in VS 2010 .net 4.0

Question

Getting this error:

System.Data.SqlClient.SqlException : The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

My entity objects all line up to the DB objects.

I found only a single reference to this error via Google:

Google result

After reading this, I remember that we did add 2 fields and then updated the entity model from VS 2010. I'm not sure what he means by "hand coding" the differences. I don't see any.

All I'm doing in code is populating the entity object and then saving. (I also populate the new fields in code) I populated the date field with DateTime.Now..

The important part of the code is this: ctx.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);

The database is SQL Server 2008.

Thoughts?

The rest of the error:

at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) at SafariAdmin.Site.WebServices.SpeciesPost.SaveOrUpdateSpecies(String sid, String fieldName, String authToken) in SpeciesPost.svc.cs: line 58 at SafariAdmin.TestHarness.Tests.Site.WebServices.SpeciesPostSVC_Tester.SaveNewSpecies() in SpeciesPostSVC_Tester.cs: line 33 --SqlException at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary2 identifierValues, List1 generatedValues) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)

Answer 1

Entity framework handles all the dates as a Datetime2, so, if your fields in the database are Datetime, this could be a problem. We had the same problem here, and from what we found, populating all the date fields and changing the datatype, are the most commom solutions

Answer 2

If you are using Code First you must declare any optional DateTime property as DateTime? or Nullable<DateTime>. Unset DateTime objects can cause issues.

If the property is nullable in the database and a standard DateTime in code (not DateTime?), ADO.NET will send an insert command with a date of 0001-01-01 (not NULL), but the minimum SQL DateTime value is 1753-01-01, causing an error. If your DateTime property in the code is nullable (e.g. DateTime? or Nullable<DateTime>), the insert command is will attempt to insert a NULL instead of an out-of-range date.

Answer 3

Use that SQL script to convert all the columns from datetime to datetime2. It skips all the tables contains 'aspnet' for your convenience.

DECLARE @SQL AS NVARCHAR(1024)
DECLARE @TBL AS NVARCHAR(255)
DECLARE @COL AS NVARCHAR(255)
DECLARE @NUL AS BIT

DECLARE CUR CURSOR FAST_FORWARD FOR
    SELECT  SCHEMA_NAME(t.schema_id)+'.'+t.name, c.name, c.is_nullable
    FROM    sys.tables AS t
    JOIN    sys.columns c ON t.object_id = c.object_id
    JOIN    information_schema.columns i ON i.TABLE_NAME = t.name 
                                        AND i.COLUMN_NAME = c.name
    WHERE   i.data_type = 'datetime' and t.name not like '%aspnet%'

    ORDER BY t.name, c.name

OPEN CUR
FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SQL = 'ALTER TABLE ' + @TBL 
        + ' ALTER COLUMN [' + @COL + '] datetime2' 
        + (CASE WHEN @NUL=1 THEN '' ELSE ' NOT' END) + ' NULL;'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL
END

CLOSE CUR;
DEALLOCATE CUR;

It works for me!

Answer 4

Another possible solution is to set the sql column type of the field to datetime2. this can be done using fluentapi.

Property(x => x.TheDateTimeField)
   .HasColumnType("datetime2");

Note: This is a solution for sql server 2008 upwards as datetime2 is not available for sql server 2005 or below.

Answer 5

I had the same problem and solve it by put the [Column(TypeName = "datetime2")] attribute to related properties, like below sample:

 [Column(TypeName = "datetime2")]
 public DateTime? PropertyName { get; set; }

Answer 6

I know that it's an old question, but as I googled here, someone else could do the same ;-) For ones that changing from DateTime to DateTime2 isn't an option (as for SQL2005 users), I think that in most cases is more reasonable to populate fields left empty with something like (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue
and not with DateTime.now, as it's easier to recognize it as a "pseudo-null" value (and if it's needed convert it to a real null in a partial class of father object)

Answer 7

When using Entity framework code first, declare it like this:

public Nullable<System.DateTime> LastLogin { get; set; }

Answer 8

Is there ModifiedTime property in your entity, which is updated on the database side only? If so, you must use DatabaseGeneratedOption.Computed (for EF CodeFirst). Also visit this https://stackoverflow.com/a/9508312/1317263

Thank you.

Answer 9

We had the same issue. This was related to the mssql version. We made it works on all of our version with this method.

Open your edmx file with an xml editor Find this line on the top of your file

<Schema Namespace="XXXXX.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008"

Replace the 2008 by 2005 Save your file, recompile the project.

Hope that will help someone else in the futur.

I only tried this solution with a dbfirst approche.

Answer 10

I had the same issue in my ASP.Net MVC application.

There were two model classes in my application that had DateTime properties.

upon investigating I noticed that the DateTime property of one model was nullable i.e. to make it date of Birth optional property

the other model had DateTime Property with Required data annotation (error occurs when saving this model)

my app is code first so I resolved the issue by setting datatype as DateTime2

[Column(TypeName="datetime2")] 

then I ran the migration on package manager console.

Answer 11

Whatever fits in a datetime will fit in a datetime2 data type, vice versa this is not the case, you can stick a date of January 1500 in a datetime2 data type but datetime only goes back to 1753, a datetime2 column can go back all the way to the year 1. I would check what the min date that you are passing in is and if your tables have datetime2 or datetime data type columns

Answer 12

After trying to solve this issue for several days I used DateTime? as the datatype in my model with Entity Framework Code-First instead of DateTime.

Answer 13

Ensure that none of the not null fields in the DB(datetime) are left out while inserting/updating. I had the same error and on inserting values to those datetime fields the issue was solved.This occurs if the not null datetime fields are not assigned a proper value.

Answer 14

Simple. On your code first, set the type of DateTime to DateTime?. So you can work with nullable DateTime type in database.

Answer 15

This follows on from stepanZ answer... I got this error when using Entity Framework Code First with AutoMapper.

When setting up the AutoMapping we have createddt, updateddt, createdby and updatedby fields which are automatically set in our public override int SaveChanges() function. When doing this you need to ensure you set these fields to be ignored by AutoMapper, otherwise the database will get updated with null for those fields when they are not supplied from the View.

My issue was that I had put the source and destination around the wrong way, therefore trying to ignore the fields when setting the ViewModel, instead of when setting the Model.

The Mapping looked like this when I recieved this error (note: the cfg.CreateMap<Source, Destination>() on the second line is mapping the Model to the ViewModel and setting the Ignore())

cfg.CreateMap<EventViewModel, Event>();
cfg.CreateMap<Event, EventViewModel>()
    .ForMember(dest => dest.CreatedBy, opt => opt.Ignore())
    .ForMember(dest => dest.CreatedDt, opt => opt.Ignore())
    .ForMember(dest => dest.UpdatedBy, opt => opt.Ignore())
    .ForMember(dest => dest.UpdatedDt, opt => opt.Ignore());

The source and destination should be ignored for a mapping from ViewModel To Model (note: The code below is correct where the Ignore() is placed against the mapping for the ViewModel to the Model)

cfg.CreateMap<Event, EventViewModel>();
cfg.CreateMap<EventViewModel, Event>()
    .ForMember(dest => dest.CreatedBy, opt => opt.Ignore())
    .ForMember(dest => dest.CreatedDt, opt => opt.Ignore())
    .ForMember(dest => dest.UpdatedBy, opt => opt.Ignore())
    .ForMember(dest => dest.UpdatedDt, opt => opt.Ignore());

Answer 16

Two solutions:

• Declare the properties in your class like below and make an update-database:

  public DateTime? MyDate {get; set;}
  

• Or set a date for the property in the Seed method that populates the database, no need of update-database :

  context.MyClass.AddOrUpdate(y => y.MyName,new MyClass { MyName = "Random", MyDate= DateTime.Now })