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

SqlDateTime.MinValue != DateTime.MinValue, why?

提问by Dhana

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

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

回答by CraigTP

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

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

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

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

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 2008 中，有许多新的基于日期的数据类型（Date、Time、DateTime2、DateTimeOffset）实际上确实提供了更大的范围和精度，并且与 .NET 中的 DateTime 数据类型紧密映射。例如，DateTime2 数据类型的日期范围从 0001-01-01 到 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:

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

从公元1年到今天，西方世界实际上使用了两种主要的历法：凯撒大帝的儒略历和教皇格里高利十三世的格里高利历。两种历法的区别仅在于一个规则：决定闰年的规则。在儒略历中，所有能被 4 整除的年份都是闰年。在公历中，所有能被 4 整除的年份都是闰年，但能被 100 整除（但不能被 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。

因此，当一个人回到很多年前时，日期算术会出现两个问题。第一个是，应该根据儒略或格里高利规则计算转换前的闰年吗？第二个问题是，跳过的日子应该在什么时候以及如何处理？

这就是八大 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 日是有效的（因为朱利安闰年适用年份规则）。当 SQL 标准似乎不需要它时，为什么 Oracle 会遇到额外的麻烦？答案是用户可能需要它。历史学家和天文学家使用这种混合系统而不是预测的公历。（这也是 Sun 在为 Java 实现 GregorianCalendar 类时选择的默认选项——尽管名称如此，但 GregorianCalendar 是一个混合日历。）

This above quotation from taken from the following link:

以上引文摘自以下链接：

SQL Performance Tuning: Dates in SQL

SQL 性能调优：SQL 中的日期

回答by Frederik Gheysels

Since, in SQL Server the minimum date that can be stored in a datetime field (1753/1/1), is not equal to the MinValue of the DateTime .NET data type (0001/1/1).

因为，在 SQL Server 中，可以存储在日期时间字段 (1753/1/1) 中的最小日期不等于 DateTime .NET 数据类型 (0001/1/1) 的 MinValue。

回答by Chris S

1753 was the date of the first adopter of the Gregorian calendar (England). As to why this was chosen over 01/01/0001 - it is no doubt legacy from when SQL Server was Sybase back in the 1990s. They must've made the design decision early on and the Microsoft SQL team haven't seen a reason to change it.

1753 年是第一个采用公历（英格兰）的日期。至于为什么在 01/01/0001 选择它 - 这无疑是 1990 年代 SQL Server 还是 Sybase 时的遗留问题。他们一定很早就做出了设计决定，而 Microsoft SQL 团队还没有看到改变它的理由。

Since the explosion of .NET and the integration of it into Sql Server, there is now the DateTime2object for compatability. If you're an NHibernate user, you can provide this typein your type mappings to avoid DateTime.Minproblems

由于 .NET 的爆炸式增长并将其集成到 Sql Server 中，现在有了DateTime2对象以实现兼容性。如果您是 NHibernate 用户，则可以在类型映射中提供此类型以避免DateTime.Min出现问题

.NET Dates cater for other calendars besides the Gregorian one:

.NET 日期迎合除公历日历之外的其他日历：

• Calendar
  • ChineseLunisolarCalendar
  • EastAsianLunisolarCalendar
  • GregorianCalendar
  • HebrewCalendar
  • HijriCalendar
  • JapaneseCalendar
  • JapaneseLunisolarCalendar
  • JulianCalendar
  • KoreanCalendar
  • KoreanLunisolarCalendar
  • PersianCalendar
  • TaiwanCalendar
  • TaiwanLunisolarCalendar
  • ThaiBuddhistCalendar
  • UmAlQuraCalendar

• 日历
  • 农历
  • 东亚阴阳历
  • 阳历日历
  • 希伯来日历
  • 回历
  • 日本日历
  • 日本阴阳历
  • 儒略历
  • 韩国日历
  • 韩国农历
  • 波斯历
  • 台历
  • 台湾月历
  • 泰国佛教日历
  • UmAlQuraCalendar

The JulianCalendar infact pre-dates DateTime.MinValue

JulianCalendar 早于日期 DateTime.MinValue

回答by Nicholas

Two different groups decided what "minimum" means to them with regard to date/time.

两个不同的小组决定了关于日期/时间的“最低限度”对他们意味着什么。

回答by leppie

SQL uses a different internal representation for DateTime.

SQL 对 DateTime 使用不同的内部表示。