SqlDateTime.MinValue！= DateTime.MinValue，为什么？ [英] SqlDateTime.MinValue != DateTime.MinValue, why?
问题描述
我认为SQL而.NET的 Date 数据类型源自SQL Server的数据类型数据类型，它的最小值和最大值，它的精度比.NET的DateTime数据类型要早。 / p>
随着.NET的出现，团队决定Datetime数据类型应该具有更多的自然最小值，而01/01/0001似乎是一个相当合乎逻辑的选择，当然也可以使用编程语言而不是数据库的观点，这个值更为自然。
顺便说一句，SQL Server 2008有一些新的基于Date的数据类型（日期，时间， DateTime2 ， DateTimeOffset ）实际上提供了更大的范围和精度，并且紧密映射到.NET中的DateTime数据类型。例如，DateTime2数据类型的日期范围从0001-01-01到9999-12-31。
SQL Server的标准datetime数据类型总是最低值为01/01/1753（确实还有！）。我必须承认，我也很好奇这个价值的意义，所以有一些挖掘..我发现如下：
在公元1世纪与今天之间的时期，西方世界实际上使用了两个主要日历：朱利叶斯·凯撒的朱利安历和教皇格雷戈里十三世的公历。这两个日历与只有一条规则不同：决定闰年的规则。在朱利安历法中，四分之一的所有年份都是闰年。在公历中，四年除以四年的所有年份都是闰年，除了百分之百（但不能被400除）的年份，并不是闰年。因此，1700年，1800年和1900年是儒略历的闰年，但不是在公历中，而1600年和2000年是两个日历的闰年。
<当教皇格雷戈里十三世在1582年提出他的日历时，他还指出，1582年10月4日至1582年10月15日的日子应该被跳过 - 也就是说，他说，10月4日之后的那一天应该是10月15日。不过，很多国家推迟了改变。英格兰及其殖民地并没有从朱利安转为公式，直到1752年为止，所以对于他们来说，跳过的日期是在1752年9月4日至9月14日之间。其他国家在其他时间转换，但1582年和1752年是相关日期我们正在讨论的DBMS。
因此，当一个可以追溯到很多年的时候，日期算术就会出现两个问题。第一个是，应该在根据朱利安或格列高利规则计算转换之前闰年？第二个问题是，何时以及如何处理被跳过的日子？
这是Big Eight DBMS处理这些问题的方法：
假装没有开关。这是SQL标准似乎需要的，虽然标准文件不清楚：它只是说日期被使用公历的日期的自然规则所约束 - 无论自然规则是什么。这是DB2选择的选项。当有一个假设，一个单一的日历的规则一直适用于没有人听到日历的时代，技术术语是激进的日历是有效的。所以，例如，我们可以说DB2遵循一个可行的公历。
完全避免这个问题。 Microsoft和Sybase在1753年1月1日设置了最低日期值，安全地超过美国切换日历的时间。这是可辩护的，但是不时，投诉表明这两个DBMS缺少其他DBMS具有的有用功能，并且SQL标准要求。
选择1582.这是Oracle做的。 Oracle用户会发现，1582年10月15日减去1582年10月4日的日期算术表达式产生1天的价值（因为10月5日至14日不存在），而1300年2月29日的日期是有效的（因为Julian leap-年规则）。为什么Oracle在SQL标准似乎不需要时会出现额外的麻烦？答案是用户可能需要它。历史学家和天文学家使用这种混合系统，而不是一个幼稚的公历。 （这也是Sun在为Java实现GregorianCalendar类时选择的默认选项，尽管名称为GregorianCalendar。GregorianCalendar是一个混合日历。）
以上引用来自以下链接：
I wonder, why SqlDateTime.MinValue is not the same as DateTime.MinValue?
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.
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.
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.
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:
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.
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?
This is how the Big Eight DBMSs handle these questions:
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.
Avoid the problem entirely. Microsoft and Sybase set their minimum date values at January 1, 1753, safely past the time that America switched calendars. This is defendable, but from time to time complaints surface that these two DBMSs lack a useful functionality that the other DBMSs have and that the SQL Standard requires.
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 Performance Tuning: Dates in SQL
这篇关于SqlDateTime.MinValue！= DateTime.MinValue，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
