我在想，为什么SqlDateTime.MinValue是不一样的DateTime.MinValue？

I wonder, why SqlDateTime.MinValue is not the same as DateTime.MinValue?

推荐答案

我想与SQL的和.NET的区别的日期的数据类型源于一个事实，即SQL Server的的日期时间数据类型，它的最小值和最大值，它是precision比.NET的日期时间数据类型大得多。

I think the difference between SQL's and .NET's Date data types stems from the fact that SQL Server's datetime data type, it's minimum and maximum values, and it's precision are much older than .NET's DateTime datatype.

随着.NET的到来，球队决定日期时间数据类型应该有更的自然的最小值，并01/01/0001似乎是一个相当合乎逻辑的选择，当然从编程语言的，而不是数据库的角度来看，这个数值是比较自然的。

With the advent of .NET, the team decided that the Datetime data type should have a more natural minimum value, and 01/01/0001 seems a fairly logical choice, and certainly from a programming language, rather than database perspective, this value is more natural.

顺便说一句，在SQL Server 2008中，也有一些新的日期型数据类型（日期，时间，的 DATETIME2 ，的DateTimeOffset ）的其实也提供了一个增加的范围和precision，密切映射到datetime数据类型的.NET。例如，DATETIME2数据类型有一个时间范围从0001-01-01通过9999-12-31

Incidentally, with SQL Server 2008, there are a number of new Date-based datatypes (Date, Time, DateTime2, DateTimeOffset) that actually do offer an increased range and precision, and closely map to the DateTime datatype in .NET. For example, the DateTime2 data type has a date range from 0001-01-01 through 9999-12-31.

标准的日期时间数据的SQL Server类型总是有过的1753年1月1日的最低值（实际上仍然有！）。我必须承认，我也很好奇这个值的意义，所以做了一些挖掘..我发现如下：

The standard "datetime" data type of SQL Server always has had a minimum value of 01/01/1753 (and indeed still does have!). I must admit, I too was curious as to the significance of this value, so did some digging.. What I found was as follows:

在1 AD和今天之间的时期，西方世界实际上已经用了两个主要的日历：凯撒的儒略历和罗马教皇格里高利十三世的公历。两种历法方面的不同只有一个规则：以决定什么是闰年是规则。在儒略历，所有年份被4整除的是闰年。在阳历中，所有年被4整除的是闰年，但多年被100整除（但不能被400整除）不闰年。因此，这些年来1700，1800和1900年是闰年的儒略历，但不是公历，而年1600年和2000年是闰年的两个日历。

During the period between 1 A.D. and today, the Western world has actually used two main calendars: the Julian calendar of Julius Caesar and the Gregorian calendar of Pope Gregory XIII. The two calendars differ with respect to only one rule: the rule for deciding what a leap year is. In the Julian calendar, all years divisible by four are leap years. In the Gregorian calendar, all years divisible by four are leap years, except that years divisible by 100 (but not divisible by 400) are not leap years. Thus, the years 1700, 1800, and 1900 are leap years in the Julian calendar but not in the Gregorian calendar, while the years 1600 and 2000 are leap years in both calendars.

当教皇格里高利十三世介绍了他的日历在1582年，他还指示，1582年10月4日和1582年10月15日之间的天，应该被忽略，也就是说，他说，10月4日之后，每天应10月15日许多国家延迟切换，虽然。英格兰和她的殖民地并没有朱利安切换到公历推算，直到1752年那么对于他们来说，跳过的日期是9月4日至九月14之间，1752年其他国家交换在其他时间，但1582以及1752是有关日期为数据库管理系统，我们正在讨论。

When Pope Gregory XIII introduced his calendar in 1582, he also directed that the days between October 4, 1582, and October 15, 1582, should be skipped—that is, he said that the day after October 4 should be October 15. Many countries delayed changing over, though. England and her colonies didn't switch from Julian to Gregorian reckoning until 1752, so for them, the skipped dates were between September 4 and September 14, 1752. Other countries switched at other times, but 1582 and 1752 are the relevant dates for the DBMSs that we're discussing.

因此​​，两个问题日期计算发生，当一个可以追溯到很多年。首先是，要跨越年前交换机根据朱利安或阳历规则计算出来的？第二个问题是，何时以及如何应该跳过日内处理？

Thus, two problems arise with date arithmetic when one goes back many years. The first is, should leap years before the switch be calculated according to the Julian or the Gregorian rules? The second problem is, when and how should the skipped days be handled?

这是大八的DBMS如何处理这些问题：

This is how the Big Eight DBMSs handle these questions:

pretend没有开关。这就是SQL标准似乎需要，虽然标准文件不清：它只是说-whatever自然法则的日期是使用公历日期受制于自然法则是。这是DB2选择的选项。当有pretence单个日历的规则总是甚至应用到时候没有人听说过的日历，技术术语是一个proleptic日历生效。因此，例如，我们可以说，DB2都遵循一个proleptic公历。

Pretend there was no switch. This is what the SQL Standard seems to require, although the standard document is unclear: It just says that dates are "constrained by the natural rules for dates using the Gregorian calendar"—whatever "natural rules" are. This is the option that DB2 chose. When there is a pretence that a single calendar's rules have always applied even to times when nobody heard of the calendar, the technical term is that a "proleptic" calendar is in force. So, for example, we could say that DB2 follows a proleptic Gregorian calendar.

避免此问题完全。 微软和Sybase设置其最小日期值在1753年1月1日，平安过去，美国切换日历的时间。这是可防御，但不时投诉表面，这两个的DBMS缺乏有用的功能，该其他DBMS具有并且SQL标准要求。

1582年匹克这是甲骨文做了。 Oracle用户会发现的时间算术前pression年10月15个1582年减去年10月4 1582年产生1天的值（因为一十月5日至14日不存在），并且所述日期年2月29日1300是有效的（因为朱利安闰年规则适用）。为什么甲骨文去额外的麻烦，当SQL标准似乎并不需要它？答案是，用户可能会需要它。历史学家和天文学家使用，而不是一个proleptic公历这种混合动力系统。 （这也是实施的GregorianCalendar类的Java尽管名字，的GregorianCalendar是一种混合日历时，Sun公司选择了默认选项。）

Pick 1582. This is what Oracle did. An Oracle user would find that the date-arithmetic expression October 15 1582 minus October 4 1582 yields a value of 1 day (because October 5–14 don't exist) and that the date February 29 1300 is valid (because the Julian leap-year rule applies). Why did Oracle go to extra trouble when the SQL Standard doesn't seem to require it? The answer is that users might require it. Historians and astronomers use this hybrid system instead of a proleptic Gregorian calendar. (This is also the default option that Sun picked when implementing the GregorianCalendar class for Java—despite the name, GregorianCalendar is a hybrid calendar.)

这是来自下面的链接上面这条语录：

This above quotation from taken from the following link:

SQL性能优化：在SQL 日期