如何修复“SqlException：将datetime2数据类型转换为日期时间数据类型导致超出范围的值。”

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

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

my code is like this:

我的代码是这样的:

        using (var contxt = new realtydbEntities())
        {
            var status = GetStatus();

            var repIssue = new RepairIssue()
            {
                CreaterId = AuthorId,
                RepairItemDesc = this.txtDescription.Text,
                CreateDate = DateTime.Now,//here's the problem
                RepairIssueStatu = status
            };

            contxt.AddObject("RepairIssues", repIssue);
            contxt.SaveChanges();
        }

the CreateDate property mapping to a column which type is smalldatetime.

CreateDate属性映射到类型为smalldatetime的列。

how to make this code run?

如何让这段代码运行?

4 个解决方案

#1

The root of your problem is that the C# DateTime object is "bigger" than SQL's smalldatetime type. Here's a good overview of the differences: http://www.karaszi.com/SQLServer/info_datetime.asp

您的问题的根源是C#DateTime对象比SQL的smalldatetime类型“更大”。以下是对差异的概述:http://www.karaszi.com/SQLServer/info_datetime.asp

So really your options are:

所以你的选择真的是:

Change the column type from smalldatetime to datetime (or datetime2)

将列类型从smalldatetime更改为datetime(或datetime2)

Instead of using EF, construct your own SQL Command (and you can use SqlDateTime)

而不是使用EF,构建自己的SQL命令(并且您可以使用SqlDateTime)

#2

I had the same exception, but it was because a non nullable datetime property that taking the min datetime value. That wasn't a smalldatetime at DB, but the min datetime of C# exceed the limit of min datetime of SQL. The solution was obvious, set the datetime properly. BTW, the code wasn't mine, and that's why I wasn't aware of that property :)

我有相同的异常,但这是因为不可为空的datetime属性采用最小日期时间值。这不是DB的小时间,但C#的最小日期时间超过了SQL的最小日期时间限制。解决方案很明显,正确设置日期时间。顺便说一句,代码不是我的,这就是为什么我不知道那个属性:)

#3

SqlDateTime will allow you to do what you need.

SqlDateTime将允许您执行所需的操作。

#4

I got this error because I had added a datetime column to my SQL table and application WITHOUT removing the old data. I found that I could update new records; but, the records that were in the table prior to the added field threw this error when an update was attempted on one of them.

我收到此错误是因为我在我的SQL表和应用程序中添加了一个datetime列而没有删除旧数据。我发现我可以更新新记录;但是,当在其中一个上尝试更新时,添加字段之前的表中的记录会引发此错误。

#1