System.Data.SqlTypes.SqlTypeException：SQLDATETIME溢出 [英] System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow

问题描述

我正与C＃.NET，也SQL Server 2008中。

I am working with C#.net and also SQL Server 2008.

我有以下错误，尝试我的项目中运行一个测试设备时。

I have the following error, when trying to run a test unit within my project.

   System.Data.SqlTypes.SqlTypeException:
   SqlDateTime overflow. Must be between
   1/1/1753 12:00:00 AM and 12/31/9999
   11:59:59 PM..

数据库表

Column Name: createddate 
Type: datetime
Default Value: (getdate())
Allow Nulls: No.

我不想插入createddate我的INSERT查询的一部分。

I don't want to insert the createddate as part of my INSERT query.

当我手动输入一些数据到数据库表中，我得到了以下错误：

When I manually enter some data into the database table I get the following error:

   The row was successfully committed
   to the database. However, a problem
   occurred when attempting  to retrieve
   the data back after commit. Because
   of this the displayed data within the
   row is read-only. To fix this
   problem, please re-run the query.

我不明白为什么我收到此错误，无法找到任何人谁拥有了这个问题。任何人都可以帮忙吗？

I don’t understand why I am getting this error and cannot find anyone who has had this problem. Can anyone help?

推荐答案

马特是最有可能在正确的轨道。您已定义为您的列的默认值 - 但是，这只会生效如果你真的插入东西在你的数据库中的表

Matt is most likely on the right track. You have defined a default value for your column - however, that will only take effect if you actually insert something in your table in the database.

当你做一个单元测试，就像你说的，你最有可能初始化DateTime变量的东西（或人 - 那么这将是DateTime.MinValue，这是01/01/0001），然后您发送到在SQL Server，该值超出了有效范围为SQL Server上的DATETIME（如错误中明确规定）。

When you do a unit test, as you say, you most likely initialize the DateTime variable to something (or not - then it'll be DateTime.MinValue, which is 01/01/0001) and then you send that to the SQL Server and this value is outside the valid range for a DATETIME on SQL Server (as the error clearly states).

所以，你需要做的是添加一行到你的.NET单元测试来初始化DateTime变量为DateTime.Today：

So what you need to do is add a line to your .NET unit test to initialize the DateTime variable to "DateTime.Today":

DateTime myDateTime = DateTime.Today

然后插入到SQL Server。

and then insert that into SQL Server.

或：你可以改变你的SQL INSERT语句，以便它不适合该列中插入一个值 - 它看起来像现在，它确实做到这一点（和试图插入 - 用于SQL Server - 无效的日期到表）。如果你不指定在INSERT列，那么该列默认 GETDATE（）将踢，插入今天的日期到列。

OR: you can change your SQL INSERT statement so that it does not insert a value for that column - it looks like right now, it does do that (and attempts to insert that - for SQL Server - invalid date into the table). If you don't specify that column in your INSERT, then the column default of getdate() will kick in and insert today's date into the column.

马克·

这篇关于System.Data.SqlTypes.SqlTypeException：SQLDATETIME溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！