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?

Solution 1

Short Answer

This can happen if you do not initialize a value to a DateTime field; the field does not accept NULL values, and it's a value type, so the default value of the non-nullable DateTime type will be used.

Setting the value fixed it for me!

Long Answer

The value of default(DateTime) is DateTime.MinValue (or new DateTime(1, 1, 1) or 01/01/0001), which is not a valid SQL datetime value.

The lowest valid value for SQL Server datetime is 01/01/1753 due to its use of a Gregorian calendar. SQL Server DateTime2 however supports dates starting at 01/01/0001. Entity Framework by default uses DateTime2 for representing dates, so the generated SQL is implicitly coercing the generated DateTime2 value to a DateTime value on the SQL Server-side.

Solution 2

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

Solution 3

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#

Solution 4

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;
}  

Solution 5

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<MyEntity>().Property(e => 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<MyEntityBaseClass>())
        {
            var values = change.CurrentValues;
            foreach (var name in values.PropertyNames)
            {
                var value = values[name];
                if (value is DateTime)
                {
                    var date = (DateTime)value;
                    if (date < SqlDateTime.MinValue.Value)
                    {
                        values[name] = SqlDateTime.MinValue.Value;
                    }
                    else if (date > SqlDateTime.MaxValue.Value)
                    {
                        values[name] = SqlDateTime.MaxValue.Value;
                    }
                }
            }
        }
    }
}

View more solutions

Comments

[Graham]

Glad to help. It's annoying because you'd think the EF data context would be able to discover that the field has a default value when the object is created from the table.

[Admin]

I'd think lots of things about EF. I'm using the POCO self tracking entities and it is such a cluster. I'm going to check out the code first model, and if that also is full of nonsense I'm seriously thinking about going back to linq to sql and using an object mapper to map props to my own entities...

[Graham]

I saw a demo of EF Code First at VS Live 2 weeks ago and it looked AWESOME, btw.

[Admin]

That's good news. We're starting up a new project soon at my office, and I'm split on EF-CF and dapper (used/maintained by SO). It'll probably come down to which is better in an app that is used via a WCF service.

[Graham]

My boss is crazy about dapper at the moment. Haven't used it yet myself but perhaps he'll grant me an experimental project to get my head wrapped around it.

[Hong]

This explains the problem that I had. Though there are few situations where real dates before 1753/1/1 need to be handled, but there are many situations where one gets the default value 0001/1/1 that may result in the error.

[Bradley Thomas]

In Entity Framework, if you add a Created column that's not null, then update your EDMX, when you're not setting the value in code, that can throw this error in this way

[Funka]

Hello, year-old comments! I myself am starting out with EF Code-First and found that on my POCO i just needed to define my datetime member as Nullable<DateTime>, and in code i can leave this truly null (instead of 01/01/0000). I was pleasantly surprised to see that EF to SQL knew to ignore this null on the INSERT and use the date from the server (GetDate())... For us this was even preferable since we needed better consistency on server, without worrying about clock differences between the webserver and that of the sql server.

[Ciaran Gallagher]

I picked up on your second point for my database models (POCO classes) and wondered how to set a field as a nullable type. In case anyone wondered, you can do this by adding a Question Mark (?) after the date type. e.g. public DateTime? StartTime { get; set; } This solved the issue for me. Only other thing I had to do was to put a TimeSpan cast around a line of code where I was subtracting two nullable DateTime values from each other. e.g. var timeTaken = (TimeSpan) (endTime - startTime);

[Adrian Carr]

Really late to the party here, but after using Dapper and POCOS at one job, and EF at another, I have to say that if you have the option to use Dapper instead of EF, it is well worth it.

[Tommy]

I am parsing this data using ExcelDataReader and it is returning the 01/01/0001 when invalid text was entered (does not throw an exception as expected - using the .GetDateTime(columnIndex) method). The comparison to the MinValue did the trick to prevent the out of range exception in sql.

[Graham]

I can also confirm what the other folks are saying about Dapper vs EF. I prefer Dapper as well.

[user3046061]

What fixed it for you? This error appears when you have a datetime field with a getdate() call as a default value.

[JoshYates1980]

Why can't the Entity Framework ignore if NULL because on my SQL side, I have a default value = getdate()?

[George Onofrei]

I confirm that when I was trying to insert a 'new DateTime()' to a 'datetime' data type I received this exception.

[L_7337]

This was it for me. But, don't forget to check models defined inside the one you are trying to create. If you have a Employee model that has a reference to a Manager, make sure the Manager object's DateTime fields are being populated properly.

[Svein Terje Gaup]

So what happens if you set it to allow nulls? Is the default value set to getDate() ? I think it might not be set...

[Ms01]

In my model I had a DateTime field that I did not set, when I did the error did not occur. During seed in database migrations.

[Guillermo Ruffino]

What I believe it happens is EntityFramework sees a DateTime.MinValue which is year 0001 and in SQL datetime is out of range value, so it sends this value as a DateTime2 (which supports year 0001) value so the insert / update is valid, however it fails when SQL tries to convert this DateTime2 to a DateTime because this will result in a different value. Two solutions are: 1 Use a nullable datetime in your model or 2. initialize all your datetime values, to the correct value before saving context changes. The choice you make depends on what the datetime means in your model.

[IDIR Samir]

This is how I declare DateTime objects in my code public DateTime ScheduledDateTime { get; set; } = DateTime.Now; `

[Mkalafut]

I was trying to assign a default value of DateTime.MinValue in my C# code that wrote to a database. This explains the error I was getting. +1

[Kiquenet]

I use Entity Framework Code First and I use model with DateTime property. DtInit = new System.DateTime(1492, 10, 12), fails.

[Kiquenet]

America Discover in 1492, wrong if not use datetime2

[Kiquenet]

Using [Column(TypeName = "datetime2")] ?

[Kiquenet]

using System.ComponentModel.DataAnnotations.Schema; is required

[Francesco B.]

@GuillermoRuffino's solution worked for me. Inspect all your fields and find those 0001 year entries.

[ΩmegaMan]

Note that I had used GETDATE() which I literally did at the time. But there was a recent comment that one should use SYSDATETIME() for any DateTime2 operations which I believe to be true.

[wha7ever]

@GuillermoRuffino 's solution worked, confirmed. Changed my DateTime to Nullable<DateTime> and it worked.

[Eugenio Miró]

This is one of those situations where the real reason is hidden behind the patch... +1

[Bartosz]

I would say passing 'DateTime.Now' as a default value is far from being correct, and is rather misleading.

[Alper]

For anyone who got confused with this; top comment is right. In mvc5 deleting the variable in the model and letting in exist in sql with a trigger to update it is working for me. I am making datetime updated with sql triggers and delete them from model so .net framework do not throw exceptions.

[AleksG]

@JoshYates1980 DateTime is a struct and cannot be null. If you do not set a DateTime property, it will have the default value, which is something like 01.01.0001 00:00:00. This again is outside of SQL Server's DATETIME value range, as marc_s points out in his answer

[Piotr Stulinski]

DateTime.Now instead of = new DateTime() solves this issue.

[joedotnot]

Please update your answer to include the new sql server "Date" type

[Savage]

What if you don't want the value to be DateTime.Now by default?

[RainyTears]

If you don't want to : DateTime.Now. You can use : new DateTime(... , ... , ...)

[cheny]

Yes, this happened twice for me. And the second time, I notice I had read this topic before and upvoted for you :D

[Ajith Chandran]

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

[Hunter-Orionnoir]

This helped me realize my audit functions weren't running on my model because the default date value was not touched. This probably wasn't the intended use for the answer, but it was the clue that rocked me. The default value for DateTime is out of range on insert. Rewind all that, I simply forgot to implement an audit info interface on our model class... after a long day that might be hard to find...