C# DateTime.MinValue 和 SqlDateTime 溢出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15157328/
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
DateTime.MinValue and SqlDateTime overflow
提问by Shree
I don't want to validate txtBirthDateso I want to pass DateTime.MinValuein database.
我不想验证txtBirthDate所以我想传入DateTime.MinValue数据库。
My code:
我的代码：
if (txtBirthDate.Text == string.Empty)
objinfo.BirthDate = DateTime.MinValue;
else
objinfo.BirthDate = DateTime.Parse(txtBirthDate.Text);
DateTime.MinValuereturn Date = {1/1/0001 12:00:00 AM}
DateTime.MinValue返回 Date = {1/1/0001 12:00:00 AM}
I got a SQL Error:
我收到一个 SQL 错误：
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
SqlDateTime 溢出。必须在 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间。
I under stand it but I don't understand why DateTime.MinValuereturn invalid date time which is unable to insert in database.How to handle this type of situation?
我明白，但我不明白为什么 DateTime.MinValue返回无法插入数据库的无效日期时间。如何处理这种情况？
采纳答案by Jon Skeet
Basically, don't use DateTime.MinValueto represent a missing value. You can'tuse DateTime.MinValuein a SQL Server DateTime field, as SQL Server has a minimum value of the start of 1753.
基本上，不要DateTime.MinValue用来表示缺失值。你不能使用DateTime.MinValue在SQL Server DateTime字段，如SQL Server有1753年开始的最小值。
Instead, make your BirthDateproperty a Nullable<DateTime>(aka DateTime?), and set it to nullwhen you don't have a value. Also make sure your database field is nullable. Then you just need to make sure that that nullends up as a NULLvalue in the database. Exactly how you do that will depend on your data access, which you haven't told us anything about.
相反，将您的BirthDate属性设为a Nullable<DateTime>（又名DateTime?），并null在您没有值时将其设置为。还要确保您的数据库字段可以为空。然后你只需要确保它null最终成为NULL数据库中的一个值。具体如何执行取决于您的数据访问权限，您没有告诉我们任何相关信息。
回答by Dave Bish
From MSDN:
来自 MSDN：
Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds. Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.
从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精度为三分之三秒，即 3.33 毫秒。值四舍五入到 .000、.003 或 0.007 毫秒的增量。存储为两个 4 字节整数。前 4 个字节存储基准日期 1900 年 1 月 1 日之前或之后的天数。基准日期是系统的参考日期。不允许使用早于 1753 年 1 月 1 日的日期时间值。其他 4 个字节存储一天中的时间，表示为午夜后的毫秒数。秒的有效范围为 0–59。
SQL uses a different system than C# for DateTime values.
SQL 对 DateTime 值使用不同于 C# 的系统。
You can use your MinValue as a sentinel value - and if it is MinValue - pass null into your object (and store the date as nullable in the DB).
您可以使用您的 MinValue 作为标记值 - 如果它是 MinValue - 将 null 传递到您的对象中（并将日期存储为可空值在数据库中）。
if(date == dateTime.Minvalue)
objinfo.BirthDate = null;
回答by Mediator
use extensions
使用扩展
public static class DateTimeExtensions
{
public static DateTime MinValue(this DateTime sqlDateTime)
{
return new DateTime(1900, 01, 01, 00, 00, 00);
}
}
DateTime date = DateTime.Now;
Console.WriteLine("Minvalue is {0} ", date.MinValue().ToShortDateString());
回答by Jens H
Simply put, don'tuse DateTime.MinVaueas a default value.
简单地说，不使用DateTime.MinVaue作为默认值。
There are a couple of different MinValuesout there, depending which environment you are in.
有几种不同的MinValues，取决于你所处的环境。
I once had a project, where I was implementing a Windows CE project, I was using the Framework's DateTime.MinValue(year 0001), the database MinValue(1753) and a UI control DateTimePicker(i think it was 1970). So there were at least 3 different MinValuesthat were leading to strange behavior and unexpected results. (And I believe that there was even a fourth (!) version, I just do not recall where it came from.).
我曾经有一个项目，我正在实施一个 Windows CE 项目，我使用的是框架DateTime.MinValue（0001 年）、数据库MinValue（1753 年）和 UI 控件DateTimePicker（我认为是 1970 年）。因此，至少有3 个不同的 MinValues导致了奇怪的行为和意外的结果。（我相信甚至还有第四个（！）版本，我只是不记得它来自哪里。）。
Use a nullable database fieldand change your value into a Nullable<DateTime>instead. Where there is no valid valuein your code, there should notbe a value in the database as well. :-)
使用可为空的数据库字段并将您的值改为 a Nullable<DateTime>。如果代码中没有有效值，则数据库中也不应该有值。:-)
回答by Sankar M
Very simple avoid using DateTime.MinValueuse System.Data.SqlTypes.SqlDateTime.MinValueinstead.
非常简单，避免使用DateTime.MinValueuseSystem.Data.SqlTypes.SqlDateTime.MinValue代替。
回答by Praveen Nambiar
Here is what you can do. Though there are lot many ways to achieve it.
这是你可以做的。虽然有很多方法可以实现它。
DateTime? d = null;
if (txtBirthDate.Text == string.Empty)
objinfo.BirthDate = d;
else
objinfo.BirthDate = DateTime.Parse(txtBirthDate.Text);
Note: This will work only if your database datetime column is Allow Null. Else you can define a standard minimum value for DateTime d.
注意：这仅在您的数据库日期时间列是 Allow Null 时才有效。否则，您可以为 DateTime d 定义标准最小值。
回答by liorlevi1974
I am using this function to tryparse
我正在使用这个函数来尝试解析
public static bool TryParseSqlDateTime(string someval, DateTimeFormatInfo dateTimeFormats, out DateTime tryDate)
{
bool valid = false;
tryDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
System.Data.SqlTypes.SqlDateTime sdt;
if (DateTime.TryParse(someval, dateTimeFormats, DateTimeStyles.None, out tryDate))
{
try
{
sdt = new System.Data.SqlTypes.SqlDateTime(tryDate);
valid = true;
}
catch (System.Data.SqlTypes.SqlTypeException ex)
{
}
}
return valid;
}
回答by Abhay Kumar
回答by Richard Barker
Well... its quite simple to get a SQL min date
嗯……获取 SQL 最小日期非常简单
DateTime sqlMinDateAsNetDateTime = System.Data.SqlTypes.SqlDateTime.MinValue.Value;
回答by David Homer
If you use DATETIME2 you may find you have to pass the parameter in specifically as DATETIME2, otherwise it may helpfully convert it to DATETIME and have the same issue.
如果您使用 DATETIME2，您可能会发现您必须专门将参数作为 DATETIME2 传递，否则它可能有助于将其转换为 DATETIME 并具有相同的问题。
command.Parameters.Add("@FirstRegistration",SqlDbType.DateTime2).Value = installation.FirstRegistration;
