错误 - SqlDateTime 溢出.必须在 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间 [英] Error - SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

问题描述

我一直在使用我编写的这段代码，它以最不清楚的方式工作.我希望在包含两列 DateTime 的数据库中插入一行:

I've been using this piece of code I've written and it's working in this most unclear manner. I wish to insert a row into the database which includes two columns of DateTime:

myrow.ApprovalDate = DateTime.Now
myrow.ProposedDate = DateTime.Now

然而，当我更新数据库时，我收到此错误:

And yet, when I update the database I receive this error:

SqlDateTime 溢出.必须在 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间.

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

我什至尝试从数据库中复制插入的值并将其硬编码到正在更新的对象中:

I've even tried copying an inserted value from the database and hard code it into the object being updated:

// I copied this value from the DB
myrow.ApprovalDate =  Convert.ToDateTime("2008-12-24 00:00:00.000");

仍然是同样的错误，奇怪的是上述技巧对第一次插入数据库有效，但从那以后就失败了.知道发生了什么吗?

Still same error, the strange part is that the above trick worked for the first insert to the DB but failed from there on. Any ideas what's going on?

推荐答案

C# 中的 DateTime 是值类型，而不是引用类型，因此不能为 null.然而，它可以是超出 Sql Servers DATETIME 数据类型范围的常量 DateTime.MinValue.

A DateTime in C# is a value type, not a reference type, and therefore cannot be null. It can however be the constant DateTime.MinValue which is outside the range of Sql Servers DATETIME data type.

值类型保证始终具有(默认)值(零)，而无需始终显式设置(在本例中为 DateTime.MinValue).

Value types are guaranteed to always have a (default) value (of zero) without always needing to be explicitly set (in this case DateTime.MinValue).

结论是您可能有一个未设置的 DateTime 值，您正试图将其传递给数据库.

Conclusion is you probably have an unset DateTime value that you are trying to pass to the database.

DateTime.MinValue = 1/1/0001 12:00:00 AM
DateTime.MaxValue = 23:59:59.9999999, December 31, 9999, 
                    exactly one 100-nanosecond tick 
                    before 00:00:00, January 1, 10000

MSDN:DateTime.MinValue

关于Sql Server

Regarding Sql Server

日期时间
从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精度为百分之三秒(相当于 3.33 毫秒或 0.00333 秒).值四舍五入到 .000、.003 或 0.007 秒的增量

datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds

小日期时间
从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟.小于等于 29.998 秒的 smalldatetime 值将四舍五入到最接近的分钟；29.999 秒或更高的值将四舍五入到最接近的分钟.

smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

MSDN:Sql Server DateTime 和 SmallDateTime

最后，如果您发现自己将 C# DateTime 作为字符串传递给 sql，则需要将其格式化为如下以保持最大精度并防止 sql server 抛出类似错误.>

Lastly, if you find yourself passing a C# DateTime as a string to sql, you need to format it as follows to retain maximum precision and to prevent sql server from throwing a similar error.

string sqlTimeAsString = myDateTime.ToString("yyyy-MM-ddTHH:mm:ss.fff");

<小时>

更新(8 年后)

---

Update (8 years later)

考虑使用 sql DateTime2 数据类型，它与日期范围 0001-01-01 到 9999-12-31 的 .net DateTime 更一致code> 和时间范围 00:00:00 到 23:59:59.9999999

Consider using the sql DateTime2 datatype which aligns better with the .net DateTime with date range 0001-01-01 through 9999-12-31 and time range 00:00:00 through 23:59:59.9999999

string dateTime2String = myDateTime.ToString("yyyy-MM-ddTHH:mm:ss.fffffff");

MSDN datetime2 (Transact-SQL)

这篇关于错误 - SqlDateTime 溢出.必须在 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！