C# 将 datetime2 数据类型转换为 datetime 数据类型会导致值超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1331779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conversion of a datetime2 data type to a datetime data type results out-of-range value
提问by Gerbrand
I've got a datatable with 5 columns, where a row is being filled with data then saved to the database via a transaction.
我有一个包含 5 列的数据表，其中一行被数据填充，然后通过事务保存到数据库中。
While saving, an error is returned:
保存时，返回错误：
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
将 datetime2 数据类型转换为 datetime 数据类型导致值超出范围
It implies, as read, that my datatable has a type of DateTime2and my database a DateTime; that is wrong.
这意味着，正如所读的，我的数据表有一个类型，DateTime2我的数据库有一个DateTime；那是错的。
The date column is set to a DateTimelike this:
日期列设置为DateTime这样：
new DataColumn("myDate", Type.GetType("System.DateTime"))
new DataColumn("myDate", Type.GetType("System.DateTime"))
Question
题
Can this be solved in code or does something have to be changed on a database level?
这可以用代码解决还是必须在数据库级别进行更改？
采纳答案by SLaks
What kind of dates do you have in the column?
你在列中有什么样的日期？
Do all of them fit within the range of the type?
它们都适合类型的范围吗？
As an aside, the correct way to get a Typeobject for the DataColumnconstructor is the typeofkeyword, which is orders of magnitude faster.
顺便说一句，Type为DataColumn构造函数获取对象的正确方法是typeof关键字，它的速度要快几个数量级。
Therefore, to create the column, you should write
因此，要创建列，您应该编写
new DataColumn("myDate", typeof(DateTime))
回答by Rob Farley
The easiest thing would be to change your database to use datetime2 instead of datetime. The compatibility works nicely, and you won't get your errors.
最简单的方法是将数据库更改为使用 datetime2 而不是 datetime。兼容性很好，你不会得到你的错误。
You'll still want to do a bunch of testing...
你仍然想做一堆测试......
The error is probably because you're trying to set a date to year 0 or something - but it all depends on where you have control to change stuff.
该错误可能是因为您试图将日期设置为第 0 年或其他日期 - 但这完全取决于您可以控制更改内容的位置。
回答by marc_s
Both the DATETIMEand DATETIME2map to System.DateTimein .NET - you cannot really do a "conversion", since it's really the same .NET type.
无论是DATETIME并DATETIME2映射到System.DateTime在.NET -你不能真正做一个“转换”，因为它是真的一样.NET类型。
See the MSDN doc page: http://msdn.microsoft.com/en-us/library/bb675168.aspx
请参阅 MSDN 文档页面：http: //msdn.microsoft.com/en-us/library/bb675168.aspx
There are two different values for the "SqlDbType" for these two - can you specify those in your DataColumndefinition?
这两个的 " SqlDbType"有两个不同的值- 你能在你的DataColumn定义中指定那些吗？
BUT: on SQL Server, the date range supported is quite different.
但是：在 SQL Server 上，支持的日期范围大不相同。
DATETIMEsupports 1753/1/1 to "eternity" (9999/12/31), while DATETIME2supports 0001/1/1 through eternity.
DATETIME支持 1753/1/1 到“永恒”（9999/12/31），同时DATETIME2支持 0001/1/1 到永恒。
So what you really need to do is check for the year of the date - if it's before 1753, you need to change it to something AFTER 1753 in order for the DATETIMEcolumn in SQL Server to handle it.
所以你真正需要做的是检查日期的年份 - 如果它在 1753 年之前，你需要将它更改为 1753 年之后的内容，以便DATETIMESQL Server 中的列处理它。
Marc
马克
回答by andyuk
This can happen if you do not assign a value to a DateTimefield when the field does not accept NULLvalues.
如果在DateTime字段不接受NULL值时未将值分配给该字段，则可能会发生这种情况。
That fixed it for me!
那为我修好了！
回答by Graham
In my SQL Server 2008 database, I had a DateTimecolumn flagged as not nullable, but with a GetDate()function as its default value. When inserting new object using EF4, I got this error because I wasn't passing a DateTime property on my object explicitly. I expected the SQL function to handle the date for me but it did not. My solution was to send the date value from code instead of relying on the database to generate it.
在我的 SQL Server 2008 数据库中，我有一DateTime列标记为不可为空，但有一个GetDate()函数作为其默认值。使用 EF4 插入新对象时，我收到此错误，因为我没有在对象上明确传递 DateTime 属性。我希望 SQL 函数为我处理日期，但它没有。我的解决方案是从代码发送日期值，而不是依赖数据库来生成它。
obj.DateProperty = DateTime.now; // C#
回答by Mahmut C
The Entity Framework 4 works with the datetime2 data type so in db the corresponding field must be datetime2 for SQL Server 2008.
实体框架 4 使用 datetime2 数据类型，因此在 db 中对应的字段必须是 SQL Server 2008 的 datetime2。
To achive the solution there are two ways.
要获得解决方案，有两种方法。
- To use the datetime data type in Entity Framwork 4 you have to switch the ProviderManifestToken in the edmx-file to "2005".
- If you set corresponding field as Allow Null (it converts it to NULLABLE) so then EF automatically uses date objects as datetime.
- 要在实体框架 4 中使用日期时间数据类型，您必须将 edmx 文件中的 ProviderManifestToken 切换为“2005”。
- 如果您将相应的字段设置为 Allow Null（它将其转换为 NULLABLE），那么 EF 会自动使用日期对象作为日期时间。
回答by Chris
In my case we were casting a Date to a Datetime and we got this error. What happens is that Date has a "more programmer oriented" minimum of 01/01/0001, while Datetime is stuck at 1753
就我而言，我们将日期转换为日期时间，但出现此错误。 发生的情况是 Date 的“更面向程序员”的最小值为 01/01/0001，而 Datetime 停留在 1753
Combine that with a data collection error on our part, and you get your exception!
将其与我们的数据收集错误相结合，您就会得到例外！
回答by sky-dev
This one was driving me crazy. I wanted to avoid using a nullable date time (DateTime?). I didn't have the option of using SQL Server 2008's datetime2type either
这让我发疯。我想避免使用可为空的日期时间 ( DateTime?)。我也没有选择使用 SQL Server 2008 的datetime2类型
modelBuilder.Entity<MyEntity>().Property(e => e.MyDateColumn).HasColumnType("datetime2");
I eventually opted for the following:
我最终选择了以下内容：
public class MyDb : DbContext
{
public override int SaveChanges()
{
UpdateDates();
return base.SaveChanges();
}
private void UpdateDates()
{
foreach (var change in ChangeTracker.Entries<MyEntityBaseClass>())
{
var values = change.CurrentValues;
foreach (var name in values.PropertyNames)
{
var value = values[name];
if (value is DateTime)
{
var date = (DateTime)value;
if (date < SqlDateTime.MinValue.Value)
{
values[name] = SqlDateTime.MinValue.Value;
}
else if (date > SqlDateTime.MaxValue.Value)
{
values[name] = SqlDateTime.MaxValue.Value;
}
}
}
}
}
}
回答by JGilmartin
for me it was because the datetime was..
对我来说这是因为日期时间是..
01/01/0001 00:00:00
01/01/0001 00:00:00
in this case you want to assign null to you EF DateTime Object... using my FirstYearRegistered code as an example
在这种情况下，您想为您的 EF DateTime 对象分配 null... 以我的 FirstYearRegistered 代码为例
DateTime FirstYearRegistered = Convert.ToDateTime(Collection["FirstYearRegistered"]);
if (FirstYearRegistered != DateTime.MinValue)
{
vehicleData.DateFirstReg = FirstYearRegistered;
}
回答by Jaime Enrique Espinosa Reyes
Sometimes it works fine on development machines and not in servers. In my case I had to put :
有时它在开发机器上工作正常，而不是在服务器上。在我的情况下，我不得不放：
<globalization uiCulture="es" culture="es-CO" />
In the web.config file.
在 web.config 文件中。
The timezone in the machine (Server) was right (to the CO locale) but the web app did not. This setting done and it worked fine again.
机器（服务器）中的时区是正确的（对于 CO 语言环境），但网络应用程序没有。此设置完成，它再次正常工作。
Off course, all dates had value.
当然，所有日期都有价值。
:D
:D
