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?

Mccollum

Answer 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.

Mobley

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 – Archilochus

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

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

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. – Riband

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

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. – Vinery

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. – Rothberg

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

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

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

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. – Suu

@Iniquitous 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 – Tiddly

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

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

Answer 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

Batts

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. – Apperceive

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

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 – Anglocatholic

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

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

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

Answer 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#

Cosmism

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. – Cosmism

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... – Lala

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

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. – Lala

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. – Cosmism

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. – Dort

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. – Hesperides

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

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

Roast

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. – Fortnight

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

Disinterest

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

Answer 6

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

1. Select the entity and the date time property
2. Select StoreGeneratedPattern
3. 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].

Dishrag

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. – Cylix

Answer 7

I ran into this and added the following to my datetime property:

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

Dives

using System.ComponentModel.DataAnnotations.Schema; is required – Citystate

Answer 8

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

Nicolanicolai

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

This is the real cause and I think this the perfect answer. – Remitter

Answer 9

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.

Blenny

towardsdatascience.com/… (Why not to use it) – Corotto

Answer 10

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

Diazonium

Answer 11

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

Flaunch

Answer 12

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.

1. To use the datetime data type in Entity Framwork 4 you have to switch the ProviderManifestToken in the edmx-file to "2005".
2. If you set corresponding field as Allow Null (it converts it to NULLABLE) so then EF automatically uses date objects as datetime.

Bobbette

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); – Ingar

Answer 13

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]

Deglutition

[DatabaseGenerated(DatabaseGeneratedOption.Computed)] – Deglutition

Answer 14

Created a base class based on @sky-dev implementation. So this can be easily applied to multiple contexts, and entities.

public abstract class BaseDbContext<TEntity> : 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<TEntity>())
        {
            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;
                    }
                }
            }
        }
    }
}

Usage:

public class MyContext: BaseDbContext<MyEntities>
{

    /// <summary>
    /// Initializes a new instance of the <see cref="MyContext"/> class.
    /// </summary>
    public MyContext()
        : base("name=MyConnectionString")
    {
    }
    /// <summary>
    /// Initializes a new instance of the <see cref="MyContext"/> class.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    public MyContext(string connectionString)
        : base(connectionString)
    {
    }

     //DBcontext class body here (methods, overrides, etc.)
 }

Sufflate

Answer 15

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!

Limitless

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... – Oatis

Answer 16

In my case, when a NULL value is explicitly assigned for Nullable DateTime column and then you try to save the changes. This error will pop up.

Octopus

Answer 17

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!

Forbore

America Discover in 1492, wrong if not use datetime2 – Citystate

Answer 18

Sometimes it works fine on development machines and not in servers. In my case I had to put :

<globalization uiCulture="es" culture="es-CO" />

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

Hemmer

Answer 19

Adding this code to a class in ASP.NET worked fort me:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));
}

Angi

Answer 20

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).

Cyna

Answer 21

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();

Mendacity

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

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

Answer 22

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

Nathan

Answer 23

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");

    }
}

Encyclical

Answer 24

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

Failing

Answer 25

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 "Default" 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.

Galah

Answer 26

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.

Bova

Answer 27

For Code First Context:

Go from this

    public DateTime Created { get; set; }

To this. Add the [DatabaseGenerated(DatabaseGeneratedOption.Computed)] attribute.

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime Created { get; set; }

in your model.

Be sure to add this to the top as well

using System.ComponentModel.DataAnnotations.Schema;

Tocology

Only works if the database actually assigns a value, which doesn't happen by only adding an attribute. See other answers. – Elisabeth

Answer 28

I came to the same error today 21/04/2024.

I was furious because I have no issue in my code but after I find out the reason I started laughing at myself.

In fact Windows Forms in Visual Studio uses default datetime2 object while Microsoft SQL Server uses default datetime object that's why you got that tricky error.

The solution is to change datetime to datetime2 in SQL Management Studio.

I attached a clear screenshot.

Cheers.

Baltoslavic

WinForms does not use datetime2. It's totally oblivious of database types whatsoever. Neither does SQL Server use DateTime by default (I mean... when?). Also, there are several answers that already tell that using DateTime2 is one of the solutions. – Elisabeth