.net - SqlDateTime.MinValue != DateTime.MinValue，为什么？

Question

我想知道，为什么 SqlDateTime.MinValue 与 DateTime.MinValue 不一样？

Answer 1

我认为 SQL 和 .NET 的 Date 数据类型之间的差异源于 SQL Server 的 datetime 数据类型、它的最小值和最大值以及它的精度比 .NET 的 DateTime 数据类型古老得多。

随着 .NET 的出现，团队决定 Datetime 数据类型应该有一个更自然的最小值，而 01/01/0001 似乎是一个相当合乎逻辑的选择，而且肯定来自 em>编程语言，而不是数据库角度，这个值更自然。

顺便说一句，在 SQL Server 2008 中，有许多新的基于日期的数据类型( Date 、 Time 、 DateTime2 、 DateTimeOffset )实际上确实提供了更大的范围和精度，并且紧密映射到 .NET 中的 DateTime 数据类型。例如，DateTime2 数据类型的日期范围为 0001-01-01 到 9999-12-31。

SQL Server 的标准“日期时间”数据类型始终具有最小值 01/01/1753(实际上仍然如此!)。我必须承认，我也很好奇这个值的意义，所以也进行了一些挖掘..我发现如下:

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.)

以上引用摘自以下链接:

SQL Performance Tuning: Dates in SQL