将datetime2数据类型转换为datetime数据类型会导致超出范围的值

I've got a datatable with 5 columns, where a row is being filled with data then saved to the database via a transaction.

我得到了一个包含5列的数据表，其中一行填充了数据，然后通过事务保存到数据库中。

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

将datetime2数据类型转换为datetime数据类型会导致超出范围的值

It implies, as read, that my datatable has a type of DateTime2 and my database a DateTime; that is wrong.

顾名思义，我的datatable有一种DateTime2类型，而我的数据库是DateTime;这是错误的。

The date column is set to a DateTime like this:

日期列设置为这样的日期时间:

new DataColumn("myDate", Type.GetType("System.DateTime"))

新上(“替换”,Type.GetType(“System.DateTime”))

Question

问题

Can this be solved in code or does something have to be changed on a database level?

这能在代码中得到解决吗?或者在数据库级别上必须改变什么?

17 个解决方案

#1

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.

顺便说一句，获取DataColumn构造函数的类型对象的正确方法是typeof关键字，它的速度快了几个数量级。

Therefore, to create the column, you should write

因此，要创建列，应该编写

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

#2

This can happen if you do not assign a value to a DateTime field when the field does not accept NULL values.

当字段不接受空值时，如果不为DateTime字段赋值，则会发生这种情况。

That fixed it for me!

帮我修好了!

#3

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.

DATETIME和DATETIME2映射到System。. net中的DateTime——你无法进行“转换”，因为它实际上是相同的。net类型。

See the MSDN doc page: http://msdn.microsoft.com/en-us/library/bb675168.aspx

请参阅MSDN doc页面: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?

对于这两个，“SqlDbType”有两个不同的值——您可以在您的DataColumn定义中指定它们吗?

BUT: on SQL Server, the date range supported is quite different.

但是:在SQL Server上，所支持的日期范围是完全不同的。

DATETIME supports 1753/1/1 to "eternity" (9999/12/31), while DATETIME2 supports 0001/1/1 through eternity.

DATETIME支持1753/1到“eternal”(9999/12/31)，而DATETIME2则支持0001/1到永恒。

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.

因此，您真正需要做的是检查日期的年份——如果是在1753年之前，您需要将它更改为1753年之后的某个值，以便SQL Server中的DATETIME列处理它。

Marc

马克

#4

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.

在我的SQL Server 2008数据库中，我有一个DateTime列标记为not nullable，但它的默认值是GetDate()函数。当使用EF4插入新对象时，我得到了这个错误，因为我没有显式地在对象上传递DateTime属性。我希望SQL函数为我处理日期，但它没有。我的解决方案是从代码中发送日期值，而不是依赖数据库生成它。

obj.DateProperty = DateTime.now; // C#

#5

for me it was because the datetime was..

对我来说，是因为日期时间。

01/01/0001 00:00:00

01/01/0001就是

in this case you want to assign null to you EF DateTime Object... using my FirstYearRegistered code as an example

在这种情况下，您希望将null分配给您的EF DateTime对象…以我的第一年注册代码为例

DateTime FirstYearRegistered = Convert.ToDateTime(Collection["FirstYearRegistered"]);
if (FirstYearRegistered != DateTime.MinValue)
{
    vehicleData.DateFirstReg = FirstYearRegistered;
}  

#6

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

这个让我抓狂。我想避免使用可空日期时间(DateTime?)我也没有使用SQL Server 2008的datetime2类型的选项

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

#7

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.

有时EF不知道它处理的是计算列或触发器。按照设计，这些操作将在插入之后设置EF之外的值。

The fix is to specify Computed in EF's edmx for that column in the StoreGeneratedPattern property.

修正是在StoreGeneratedPattern属性中为该列在EF的edmx中指定计算结果。

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

在Visual Studio中打开模型浏览器页面，然后模型实体类型——>

1. Select the entity and the date time property
2. 选择实体和日期时间属性
3. Select StoreGeneratedPattern
4. 选择StoreGeneratedPattern
5. Set to Computed
6. 将计算

---

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:

对于这种情况，其他答案都是变通方法，对于列的目的是在创建记录时指定时间/日期，这是SQL执行触发器以添加正确时间的任务。例如这个SQL触发器:

DEFAULT (GETDATE()) FOR [DateCreated].

默认(获取当前日期())[DateCreated]。

#8

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

我遇到了这个，并在我的datetime属性中添加了以下内容:

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

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

最简单的方法是将数据库改为使用datetime2而不是datetime。兼容性很好，不会出现错误。

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.

错误可能是因为您试图将日期设置为0年或其他什么——但这一切都取决于您控制更改内容的位置。

#10

If we dont pass a date time to date time field the default date {1/1/0001 12:00:00 AM} will be passed.

如果我们不传递日期时间到日期时间字段默认日期{1/ 01 12:00:00 AM}将被传递。

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

但是这个日期与实体框架工作不兼容，因此它会将datetime2数据类型转换为datetime数据类型，从而导致值超出范围

Just default DateTime.now to the date field if you are not passing any date .

只是默认DateTime。如果你没有通过任何日期，现在到日期字段。

movie.DateAdded = System.DateTime.Now

#11

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.

将datetime2数据类型转换为datetime数据类型会导致超出范围的值。

Use a nullable DateTime object.
public DateTime? PurchaseDate { get; set; }

使用可空的DateTime对象。公共DateTime ?PurchaseDate {得到;设置;}

If you are using entity framework Set the nullable property in the edmx file to True

如果您正在使用实体框架，请将edmx文件中的nullable属性设置为True

#12

The Entity Framework 4 works with the datetime2 data type so in db the corresponding field must be datetime2 for SQL Server 2008.

实体框架4使用datetime2数据类型，因此在db中，SQL Server 2008的相应字段必须是datetime2。

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. 要在实体框架4中使用datetime数据类型，您必须将edmx-file中的ProviderManifestToken转换为“2005”。
3. If you set corresponding field as Allow Null (it converts it to NULLABLE) so then EF automatically uses date objects as datetime.
4. 如果您将相应的字段设置为允许Null(它将其转换为NULLABLE)，那么EF将自动使用日期对象作为datetime。

#13

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.

正如andyuk已经指出的那样，当一个空值被分配给一个不可空的DateTime字段时，就会发生这种情况。考虑将DateTime更改为DateTime?或可空< DateTime >。请记住，在使用依赖属性时，还应该确保依赖属性的类型也是可空的DateTime类型。

Below is a real life example of an incomplete DateTime to DateTime? type adjustment that raises the odd behaviour

下面是一个不完整的DateTime到DateTime的实例?引发奇怪行为的类型调整

#14

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

基于@sky-dev实现创建一个基类。因此，这可以很容易地应用于多个上下文和实体。

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

#15

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

在我的例子中，我们将日期转换为Datetime，然后我们得到了这个错误。实际情况是，Date的“更多面向程序员”最小值为01/01/0001，而Datetime的值为1753

Combine that with a data collection error on our part, and you get your exception!

结合我们的数据收集错误，您将得到您的异常!

#16

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.

在web。配置文件。

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.

机器(服务器)中的时区(对于CO语言环境)是正确的，但是web应用程序没有。这个设置完成了，它再次运行良好。

Off course, all dates had value.

当然，所有的日期都有价值。

:D

:D

#17

you will have date column which was set to lesathan the min value of allowed dattime like 1/1/1001.

将日期列设置为lesathan，即允许的日期时间的最小值，如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.

为了解决这个问题，您可以将适当的datetime值设置为ur属性adn，还可以设置另一个神奇的属性，如IsSpecified=true。