entityMap|blocks|key|afi2i|text|这通常意味着向查询发送的是null，而不是所需的值，您可以尝试运行SQL事件探查器，以查看从linq传递到SQL+Server的确切内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|5|6|7|8|C|9|@]|A|@]|B|$]]]]

That usually means a null is being posted to the query instead of your desired value, you might try to run the SQL Profiler to see exactly what is getting passed to SQL Server from linq.

entityMap|blocks|key|7dsdd|text|这两列的代码看起来没问题。查找该映射类上的任何其他日期时间列。另外，在datacontext上启用日志记录，以查看查询和参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|epk27|dc.Log+=+Console.Out;|code-block|syntax|js|uvqg|DateTime被初始化为c#的0，即0001-01-01。这由linqtosql通过sql字符串文字传输到数据库：'0001-01-01‘。Sql无法从此日期分析T-Sql日期时间。|7g3gk|有几种方法可以解决这个问题：|b2akb|确保使用SQL可以处理的值初始化所有日期时间(例如Sql的0:+1900-01-01+)|unordered-list-item|r0fa|确保可能偶尔省略的任何日期时间都是可空的日期时间^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|Q|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|R|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|S|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|T|9|@]|A|@]|B|$]]|$2|L|4|M|6|N|8|U|9|@]|A|@]|B|$]]|$2|O|4|P|6|N|8|V|9|@]|A|@]|B|$]]]]

The code you have for the two columns looks ok. Look for any other datetime columns on that mapping class. Also, enable logging on the datacontext to see the query and parameters.

<pre><code>dc.Log = Console.Out;
</code></pre>

DateTime is initialized to c#'s 0 - which is 0001-01-01. This is transmitted by linqtosql to the database via sql string literal : '0001-01-01'. Sql cannot parse a T-Sql datetime from this date.

There's a couple ways to deal with this:

<ul>
<li>Make sure you initialize all date times with a value that SQL can handle (such as Sql's 0 : 1900-01-01 )</li>
<li>Make sure any date times that may occasionally be omitted are nullable datetimes</li>
</ul>

entityMap|0|type|LINK|mutability|MUTABLE|data|url|http://msdn.microsoft.com/en-us/library/system.datetime.minvalue.aspx|1|HR|IMMUTABLE|2|https://www.microsoft.com/en-us/download/details.aspx?id=51958|3|4|5|https://msdn.microsoft.com/en-us/library/bb677335.aspx|blocks|key|21o66|text|ADateTimein+C#是值类型，而不是引用类型，因此不能为null。但是，它可以是常量DateTime.MinValue，它不在Sql服务器的范围内。DATETIME数据类型。|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|1qhle|值类型保证总是有一个(默认)值(0)，而不需要总是显式设置(在本例中是DateTime.MinValue)。|4g2jt|结论是您可能有一个试图传递给数据库的未设置的DateTime值。|j0bm|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|code-block|syntax|javascript|brq88|MSDN：DateTime.MinValue|8424c|----|atomic|cncj6|关于Sql+Server|cojl2|日期时间|blockquote|2103e|从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之一秒(相当于3.33毫秒或0.00333秒)。将值四舍五入为.000、.003或.007秒的增量|7d6i0|小日期时间|amccf|从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。小于或等于29.998秒的SmalldateTime值向下舍入到最接近的分钟；大于或等于29.999秒的值向上舍入到最接近的分钟。|qf39|MSDN：Sql+Server+DateTime和SmallDateTime|d3t88|12jl0|最后，如果您发现自己正在传递一个C#DateTime|32kd7|作为sql的字符串，您需要将其格式化为以下格式，以保持最大的精度并防止sql+server引发类似的错误。|2ipdi|string+sqlTimeAsString+=+myDateTime.ToString("yyyy-MM-ddTHH:mm:ss.fff");|js|84b6g|5bja2|更新(8年后)|ejds2|考虑使用sqlDateTime2更符合.net的数据类型DateTime包含日期范围0001-01-01+through+9999-12-31|bfbo3|和时间范围00:00:00+through+23:59:59.9999999|51fev|string+dateTime2String+=+myDateTime.ToString("yyyy-MM-ddTHH:mm:ss.fffffff");|b1i3a|MSDNdatetime2+(Transact-SQL)^0|1|8|1A|H|26|8|0|0|0|0|5|H|0|0|0|4|1|0|0|0|0|0|0|5|X|2|0|0|4|3|0|I|8|0|0|0|0|4|4|0|0|7|9|S|8|16|T|0|5|X|0|0|4|O|5^^$0|$1|$2|3|4|5|6|$7|8]]|9|$2|A|4|B|6|$]]|C|$2|3|4|5|6|$7|D]]|E|$2|A|4|B|6|$]]|F|$2|A|4|B|6|$]]|G|$2|3|4|5|6|$7|H]]]|I|@$J|K|L|M|2|N|O|25|P|@$Q|26|R|27|S|T]|$Q|28|R|29|S|T]|$Q|2A|R|2B|S|T]]|U|@]|6|$]]|$J|V|L|W|2|N|O|2C|P|@]|U|@]|6|$]]|$J|X|L|Y|2|N|O|2D|P|@]|U|@]|6|$]]|$J|Z|L|10|2|11|O|2E|P|@]|U|@]|6|$12|13]]|$J|14|L|15|2|N|O|2F|P|@]|U|@$Q|2G|R|2H|J|2I]]|6|$]]|$J|16|L|17|2|18|O|2J|P|@]|U|@$Q|2K|R|2L|J|2M]]|6|$]]|$J|19|L|1A|2|N|O|2N|P|@]|U|@]|6|$]]|$J|1B|L|1C|2|1D|O|2O|P|@]|U|@]|6|$]]|$J|1E|L|1F|2|1D|O|2P|P|@]|U|@]|6|$]]|$J|1G|L|1H|2|1D|O|2Q|P|@]|U|@]|6|$]]|$J|1I|L|1J|2|1D|O|2R|P|@]|U|@]|6|$]]|$J|1K|L|1L|2|N|O|2S|P|@]|U|@$Q|2T|R|2U|J|2V]]|6|$]]|$J|1M|L|17|2|18|O|2W|P|@]|U|@$Q|2X|R|2Y|J|2Z]]|6|$]]|$J|1N|L|1O|2|N|O|30|P|@$Q|31|R|32|S|T]]|U|@]|6|$]]|$J|1P|L|1Q|2|N|O|33|P|@]|U|@]|6|$]]|$J|1R|L|1S|2|11|O|34|P|@]|U|@]|6|$12|1T]]|$J|1U|L|17|2|18|O|35|P|@]|U|@$Q|36|R|37|J|38]]|6|$]]|$J|1V|L|1W|2|N|O|39|P|@]|U|@]|6|$]]|$J|1X|L|1Y|2|N|O|3A|P|@$Q|3B|R|3C|S|T]|$Q|3D|R|3E|S|T]|$Q|3F|R|3G|S|T]]|U|@]|6|$]]|$J|1Z|L|20|2|N|O|3H|P|@$Q|3I|R|3J|S|T]]|U|@]|6|$]]|$J|21|L|22|2|11|O|3K|P|@]|U|@]|6|$12|1T]]|$J|23|L|24|2|N|O|3L|P|@]|U|@$Q|3M|R|3N|J|3O]]|6|$]]]]

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

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.

<pre><code>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
</code></pre>

MSDN: <a href="http://msdn.microsoft.com/en-us/library/system.datetime.minvalue.aspx" rel="noreferrer">DateTime.MinValue</a> 

<hr>

Regarding Sql Server

<blockquote>
 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 
 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.
</blockquote>

MSDN: <a href="http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx" rel="noreferrer">Sql Server DateTime and SmallDateTime</a>

<hr>

Lastly, if you find yourself passing a C# <code>DateTime</code> 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.

<pre><code>string sqlTimeAsString = myDateTime.ToString("yyyy-MM-ddTHH:mm:ss.fff");
</code></pre>

<hr>

Update (8 years later)

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

<pre><code>string dateTime2String = myDateTime.ToString("yyyy-MM-ddTHH:mm:ss.fffffff");
</code></pre>

MSDN <a href="https://msdn.microsoft.com/en-us/library/bb677335.aspx" rel="noreferrer">datetime2 (Transact-SQL)</a>

entityMap|blocks|key|7alhe|text|有时，为了编写更少的代码，通常使用SQL+server在插入时设置字段，如日期、时间和ID，方法是将字段的默认值设置为GETDATE()或者NEWID()..。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|ac22p|在这种情况下自动生成的值属性应设置为true。这样你就不需要在代码中设置值(防止能源消耗！)永远看不到这个例外。^0|1N|9|1Y|7|0^^$0|$]|1|@$2|3|4|5|6|7|8|I|9|@$A|J|B|K|C|D]|$A|L|B|M|C|D]]|E|@]|F|$]]|$2|G|4|H|6|7|8|N|9|@]|E|@]|F|$]]]]

Sometimes in order to write less code it is used to have SQL server set fields like date, time and ID on insert by setting the default value for fields to <code>GETDATE()</code> or <code>NEWID()</code>.

In such cases Auto Generated Value property of those fields in entity classes should be set to true.

This way you do not need to set values in code (preventing energy consumption!!!) and never see that exception.

entityMap|blocks|key|19r8a|text|我发现在发生了许多与数据库相关的错误后，使用下面的方法可以很好地处理SQL最小/最大日期：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|l8eb|DateTime+rngMin+=+(DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;

DateTime+rngMax+=+(DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue;|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

I find using the following works quite well for SQL min/max dates after many DB related errors:

<pre><code>DateTime rngMin = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;

DateTime rngMax = (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue;
</code></pre>

entityMap|blocks|key|eqgv8|text|如果您使用的是NHibernate，请检查映射中相应的可为空的DateTime属性是否设置为可为空。|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|5|6|7|8|C|9|@]|A|@]|B|$]]]]

If you are using NHibernate, check that appropriate DateTime properties that are nullable are set to nullable in the mappings.

entityMap|blocks|key|7smse|text|在将.Net+DateTime与SqlDateTime.MinValue或MaxValue进行比较时要小心。例如，以下代码将抛出异常：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5eho4|DateTime+dte+=+new+DateTime(1000,+1,+1);
if+(dte+>=+SqlDateTime.MinValue)
++++//do+something|code-block|syntax|javascript|eu5u|原因是MinValue返回的是SqlDateTime，而不是DateTime。因此，.Net尝试将dte转换为SqlDateTime进行比较，因为它超出了可接受的SqlDateTime范围，所以会抛出异常。|7iis8|一种解决方案是将您的DateTime与SqlDateTime.MinValue进行比较。价值..。^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|O|9|@]|A|@]|B|$]]]]

Beware when comparing a .Net DateTime to SqlDateTime.MinValue or MaxValue. For example, the following will throw an exception:

<pre><code>DateTime dte = new DateTime(1000, 1, 1);
if (dte &gt;= SqlDateTime.MinValue)
 //do something
</code></pre>

The reason is that MinValue returns a SqlDateTime, not a DateTime. So .Net tries to convert dte to a SqlDateTime for comparison and because it's outside the acceptable SqlDateTime range it throws the exception.

One solution to this is to compare your DateTime to SqlDateTime.MinValue.Value.

entityMap|blocks|key|cf5q|text|我也看到了同样的事情。该错误不是在插入行时发生，而是在更新时发生。我引用的表有两个DateTime列，这两个列都不能为空。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6a1lm|我已经了解了获取行并立即保存它的场景(没有数据更改)。get运行正常，但更新失败。|6kanm|我们使用的是NHibernate+3.3.1.4000^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|G|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|H|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|I|9|@]|A|@]|B|$]]]]

I am seeing the same thing. The error does not happen on insert of a row but on an update. the table I am referencing has two DateTime columns neither of which are nullable. 

I have gotten the scenario down to getting the row and immediately saving it (no data changes). The get works fine but the update fails.

We are using NHibernate 3.3.1.4000

entityMap|blocks|key|bicpq|text|通常在进行DateTime转换或解析时会出现这种错误。检查托管应用程序的服务器中的日历设置，主要是时区和短日期格式，并确保为该位置设置了正确的时区。希望这能解决这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|5|6|7|8|C|9|@]|A|@]|B|$]]]]

Usually this kind of error comes when you do DateTime conversion or parsing. Check the calendar setting in the server where the application is hosted, mainly the time zone and short date format, and ensure it's set to the right time zone for the location. Hope this would resolve the issue.

entityMap|blocks|key|f65if|text|DateTime.MinValue和DateTime.MaxValue|type|unstyled|depth|inlineStyleRanges|entityRanges|data|cfm64|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|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

DateTime.MinValue and DateTime.MaxValue 

<pre><code>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 
</code></pre>

entityMap|blocks|key|7pl6k|text|如果您正在尝试设置以下类型的变量，则会出现此错误|type|unstyled|depth|inlineStyleRanges|entityRanges|data|a4euc|DateTime至空..。将变量声明为可空，即DateTime？..。这将解决问题。|offset|length|style|BOLD^0|0|N|8^^$0|$]|1|@$2|3|4|5|6|7|8|I|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|J|9|@$E|K|F|L|G|H]]|A|@]|B|$]]]]

This error occurs if you are trying to set variable of type DateTime to null. Declare the variable as nullable, i.e. DateTime? . This will solve the problem.

entityMap|blocks|key|frghs|text|使用扩展方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6inrk|public+static+object+ToSafeDbDateDBnull(this+object+objectstring)
++++{
++++++++try
++++++++{
++++++++++++if+((DateTime)objectstring+>=+SqlDateTime.MinValue)
++++++++++++{
++++++++++++++++return+objectstring;
++++++++++++}
++++++++++++else
++++++++++++{
++++++++++++++++return+DBNull.Value;
++++++++++++}
++++++++}
++++++++catch+(Exception)
++++++++{

++++++++++++return+DBNull.Value;
++++++++}

++++}

DateTime+objdte+=+new+DateTime(1000,+1,+1);
dte.ToSafeDbDateDBnull();|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Use extension method 

<pre><code> public static object ToSafeDbDateDBnull(this object objectstring)
 {
 try
 {
 if ((DateTime)objectstring &gt;= SqlDateTime.MinValue)
 {
 return objectstring;
 }
 else
 {
 return DBNull.Value;
 }
 }
 catch (Exception)
 {

 return DBNull.Value;
 }

 }

DateTime objdte = new DateTime(1000, 1, 1);
dte.ToSafeDbDateDBnull();
</code></pre>

entityMap|blocks|key|4iqkb|text|如果您将Datetime设置为可空，如下所示DateTime?在你的模型中它不会抛出异常。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4fekf|在我的案例中，我就像这样解决了这个问题。^0|M|9|0^^$0|$]|1|@$2|3|4|5|6|7|8|I|9|@$A|J|B|K|C|D]]|E|@]|F|$]]|$2|G|4|H|6|7|8|L|9|@]|E|@]|F|$]]]]

If you put Datetime nullable like <code>DateTime?</code> in your model it doesn't throw exception.
I solved the problem like this in my case.

entityMap|blocks|key|dssv4|text|在我的示例中，引发此错误是因为表日期列不能为空|type|unstyled|depth|inlineStyleRanges|entityRanges|data|cebf1|具体如下：|cf6qh|Create+Table+#TempTable(
+...
+ApprovalDate+datatime+not+null.
+...)|code-block|syntax|javascript|aaegj|要避免此错误，只需将其设置为null-able即可|pf6|Create+Table+#TempTable(
+...
+ApprovalDate+datatime+null.
+...)^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|O|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|P|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|Q|9|@]|A|@]|B|$]]|$2|L|4|M|6|G|8|R|9|@]|A|@]|B|$H|I]]]]

In my case this error was raised because table date column is not null-able
As below:
<pre><code>Create Table #TempTable(
 ...
 ApprovalDate datatime not null.
 ...)
</code></pre>
To avoid this error just make it null-able
<pre><code> Create Table #TempTable(
 ...
 ApprovalDate datatime null.
 ...)
</code></pre>

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:

<pre><code>myrow.ApprovalDate = DateTime.Now
myrow.ProposedDate = DateTime.Now
</code></pre>

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

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

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

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

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?

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

SQL Server

数据库

服务器

 我一直在使用我写的这段代码，它以一种最不明确的方式工作。我希望在包含两列DateTime的数据库中插入一行： myrow.ApprovalDate = DateTime.Nowmyrow.ProposedDate = DateTime.Now 然而，当我更新数据库时，我收到了这个错误： SqlDateTime溢出。必...

问错误- SqlDateTime溢出。必须在1/1/1753 12:00:00 AM和12/31/9999 11:59:59 PM之间
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问错误- SqlDateTime溢出。必须在1/1/1753 12:00:00 AM和12/31/9999 11:59:59 PM之间EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问错误- SqlDateTime溢出。必须在1/1/1753 12:00:00 AM和12/31/9999 11:59:59 PM之间
EN