错误 - SQLDATETIME溢出。必须是1753年1月1日12:00:00 AM和12/31/9999下午11:59:59之间 [英] Error - SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

问题描述

我一直在使用这片code的我已经写了，它的工作在这个最清楚的方式。我想插入一行，其中包括日期时间的两列的数据库：

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溢出。必须是1753年1月1日12:00:00 AM和12/31/9999下午11:59:59之间。

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

我甚至试图复制从数据库和硬code将其插入对象插入值被更新：

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?

推荐答案

A 的DateTime 是值类型，不是引用类型，因此不能为空。然而，它可以是常量 DateTime.MinValue 是的SQL服务器 DATETIME 数据类型的范围之外。

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

结论是，你可能有你试图传递给数据库的未设置日期时间值。

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服务器

日期时间结果
  从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

SMALLDATETIME结果
  从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，则需要按如下方式保留最大precision和$ p来格式化从抛出类似的错误$ pvent SQL服务器。

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")

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