将datetime2数据类型转换为smalldatetime数据类型导致值超出范围.\ r \ n该语句已终止 [英] The conversion of a datetime2 data type to a smalldatetime data type resulted in an out-of-range value.\r\nThe statement has been terminated

问题描述

我制作了一个小型MVC4应用程序，并在本地SQL 2012服务器上制作了数据库.我从2012年导入"了该数据，将其移动到了廉价的共享主机上(使用sql 2008生产).对非隶属关系表进行了一些更改，并从sql 2008中删除了表并重新导入. 不是由于某种原因我会收到此错误

I made a small MVC4 app and made database on my local SQL 2012 server. "imported" that data from my 2012 moved it to my cheap shared host as production with sql 2008. everything worked fine. made some changes to non-aspmembership tables and deleted tables from sql 2008 and reimported. not for some reason i get this error

The conversion of a datetime2 data type to a smalldatetime data type resulted in an out-of-range value.\r\nThe statement has been terminated

如果我尝试注册一个帐户.我尝试将所有smalldatetimecolumns更改为datetime2列，但是由于某种原因，即使没有smalldatetimes，也会发生相同的错误?

if i try to register an account. i tried chaning all of the smalldatetimecolumns to datetime2 columns but for some reason the same error occurs even though there are no smalldatetimes?

有人有什么想法吗?谢谢.

Does anyone have any ideas? thank you.

edit:未来的人们-我不确定为什么要解决此问题，但是我在SQL Express 2008中而不是SQL 2012中重新创建了表，然后将它们移了过来.工作正常.

edit: people of the future - i am unsure why this fixed it, but i recreated the tables in SQL express 2008 instead of SQL 2012 and then moved them over. worked fine.

推荐答案

从错误的外观来看，您的数据库使用SmallDateTime作为日期类型.其最小值是1900年1月1日.假设您的实体如下所示

From the look of the error, your database is using SmallDateTime as date type. Its min value is 1900-Jan-01. Assuming your entity is something like below

  public class Game
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid GameId { get; set; }

    [Required]
    public DateTime GameTime { get; set; }
  }

您的GameTime值不可为空，因此，当您不为其设置任何值时，它将始终是DateTime.Min，它是0000-Jan-01.这不在SmallDateTime的范围内，但在DateTime2的范围内.因此，EF将尝试将DateTime2 SQL类型传递给SQL服务器.由于您的数据库使用的是SmallDateTime，因此您会看到问题中显示的错误.

Your GameTime value is not nullable, so when you don't set any value for it, it will always be the DateTime.Min which is 0000-Jan-01. This is out of range of SmallDateTime, but it is in range of DateTime2. So EF will try to pass a DateTime2 SQL type to the SQL server. Because your database is using SmallDateTime, you will get the error shown in your question.

要解决此问题，您可以考虑以下选项:

To resolve the issue, you have following options that I can think of:

• 使该字段为null. (您必须始终检查您的输入日期是否在SmallDateTime范围内.)

• make the field nullalbe. (you have to always check if your input dates are within the SmallDateTime range.)

或在SQL Server中将日期类型更改为DateTime2

or change the date type to DateTime2 in SQL server

或在创建数据库时强制EF使用DateTime2数据类型.该代码将类似于以下内容. (此方法应该在您的上下文类中.)

or force EF to use DateTime2 data type when creating database. The code will be like something below. (This method should inside of your context class.)

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .Property(t => t.GameTime )
            .HasColumnType("datetime2");
    }

• 写一个转换器助手来设置DateTime属性的最小值，以匹配应用程序中的smalldatetime.

  • Write a converter helper to set your DateTime property min value to match smalldatetime in your application.

    在数据库中编写一个触发器，以将DateTime2(从应用程序端，但在数据库端为datetime2)转换为smalldatetime.

    Write a trigger in the database to convert DateTime2 (from application end, but datetime2 in database end) to smalldatetime.

    希望我能正确理解您的问题，我的回答会有所帮助.

    Hopefully, I have understood your question properly, and my answer helps.

    这篇关于将datetime2数据类型转换为smalldatetime数据类型导致值超出范围.\ r \ n该语句已终止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！