SQL Server 中的 DateTime2 与 DateTime

DateTime2 vs DateTime in SQL Server

提问by Mikeon

Which one:

哪一个：

• datetime
• datetime2

• datetime
• datetime2

is therecommended way to store date and time in SQL Server 2008+?

在 SQL Server 2008+ 中存储日期和时间的推荐方法是什么？

I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2only?

我知道精度（可能还有存储空间）的差异，但现在忽略这些差异，是否有关于何时使用什么的最佳实践文档，或者我们应该只使用datetime2什么？

采纳答案by Adam Porad

The MSDN documentation for datetimerecommends using datetime2. Here is their recommendation:

datetime的 MSDN 文档建议使用datetime2。这是他们的建议：

Use the time, date, datetime2and datetimeoffsetdata types for new work. These types align with the SQL Standard. They are more portable. time, datetime2and datetimeoffsetprovide more seconds precision. datetimeoffsetprovides time zone support for globally deployed applications.

使用time，date，datetime2和 datetimeoffset数据类型的新工作。这些类型符合 SQL 标准。它们更便携。 time，datetime2并datetimeoffset提供更多的秒精度。 datetimeoffset为全球部署的应用程序提供时区支持。

datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage.

datetime2 具有更大的日期范围、更大的默认小数精度和可选的用户指定精度。此外，根据用户指定的精度，它可能会使用较少的存储空间。

回答by marc_s

DATETIME2has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIMEtype only supports year 1753-9999.

DATETIME2日期范围为“0001 / 01 / 01”到“9999 / 12 / 31”，而该DATETIME类型仅支持 1753-9999 年。

Also, if you need to, DATETIME2can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2can be accurate down to 100ns.

此外，如果需要，DATETIME2可以在时间方面更精确；DATETIME 限制为 3 1/3 毫秒，而DATETIME2可以精确到 100ns。

Both types map to System.DateTimein .NET - no difference there.

两种类型都映射到System.DateTime.NET - 没有区别。

If you have the choice, I would recommend using DATETIME2whenever possible. I don't see any benefits using DATETIME(except for backward compatibility) - you'll have less trouble (with dates being out of range and hassle like that).

如果您有选择，我建议您DATETIME2尽可能使用。我没有看到使用的任何好处DATETIME（除了向后兼容性） - 你会遇到更少的麻烦（日期超出范围和麻烦）。

Plus: if you only need the date (without time part), use DATE - it's just as good as DATETIME2and saves you space, too! :-) Same goes for time only - use TIME. That's what these types are there for!

另外：如果您只需要日期（没有时间部分），请使用 DATE - 它DATETIME2与节省空间一样好！:-) 同样适用于时间 - 使用TIME。这就是这些类型的用途！

回答by Iman

datetime2wins in most aspects except (old apps Compatibility)

datetime2在大多数方面都胜出（旧应用程序兼容性）

1. larger range of values
2. better Accuracy
3. smaller storage space(if optional user-specified precision is specified)

1. 更大范围的值
2. 更好的准确性
3. 更小的存储空间（如果指定了可选的用户指定精度）

please note the following points

请注意以下几点

• Syntax
  • datetime2[(fractional seconds precision=> Look Below Storage Size)]
• Precision, scale
  • 0 to 7 digits, with an accuracy of 100ns.
  • The default precision is 7 digits.
• Storage Size
  • 6 bytes for precision less than 3;
  • 7 bytes for precision 3 and 4.
  • All other precision require 8 bytes.
• DateTime2(3)have the same number of digits as DateTime but uses 7 bytes of storage instead of 8 byte (SQLHINTS- DateTime Vs DateTime2)
• Find more on datetime2(Transact-SQL MSDN article)

• 句法
  • datetime2[（小数秒精度=> 看下面的存储大小）]
• 精度、规模
  • 0～7位，精度100ns。
  • 默认精度为 7 位。
• 存储大小
  • 精度小于 3 时为 6 个字节；
  • 7 个字节用于精度 3 和 4。
  • 所有其他精度都需要 8 个字节。
• DateTime2(3)具有与 DateTime 相同的位数，但使用 7 个字节的存储空间而不是 8 个字节（SQLHINTS-DateTime Vs DateTime2）
• 在datetime2上查找更多信息（Transact-SQL MSDN 文章）

image source : MCTS Self-Paced Training Kit (Exam 70-432): Microsoft? SQL Server? 2008 - Implementation and MaintenanceChapter 3:Tables -> Lesson 1: Creating Tables -> page 66

图片来源： MCTS 自定进度培训套件（考试 70-432）：Microsoft？SQL服务器？2008 - 实施和维护第 3 章：表 -> 第 1 课：创建表 -> 第 66 页

回答by EBarr

I concurr with @marc_s and @Adam_Poward -- DateTime2 is the preferred method moving forward. It has a wider range of dates, higher precision, and uses equal or less storage (depending on precision).

我同意@marc_s 和@Adam_Poward——DateTime2 是前进的首选方法。它的日期范围更广，精度更高，并且使用相等或更少的存储空间（取决于精度）。

One thing the discussion missed, however...
@Marc_s states: Both types map to System.DateTime in .NET - no difference there. This is correct, however, the inverse is not true...and it matters when doing date range searches (e.g. "find me all records modified on 5/5/2010").

然而，讨论遗漏了一件事......
@Marc_s 声明：Both types map to System.DateTime in .NET - no difference there。这是正确的，但是，反之则不正确......并且在进行日期范围搜索时很重要（例如“找到我在 5/5/2010 修改的所有记录”）。

.NET's version of Datetimehas similar range and precision to DateTime2. When mapping a .net Datetimedown to the old SQL DateTimean implicit rounding occurs. The old SQL DateTimeis accurate to 3 milliseconds. This means that 11:59:59.997is as close as you can get to the end of the day. Anything higher is rounded up to the following day.

.NET 版本的 Datetime范围和精度与DateTime2. 当映射.NETDatetime下到老SQLDateTime的出现隐含四舍五入。旧的 SQLDateTime精确到 3 毫秒。这意味着这11:59:59.997将尽可能接近一天的结束。任何更高的值都会向上舍入到第二天。

Try this :

尝试这个 ：

declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'

Avoiding this implicit rounding is a significant reason to move to DateTime2. Implicit rounding of dates clearly causes confusion:

避免这种隐式舍入是转移到 DateTime2 的重要原因。日期的隐式四舍五入显然会导致混淆：

• Strange datetime behavior in SQL Server
• http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
• SQL Server 2008 and milliseconds
• http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspx
• http://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx

• SQL Server 中的奇怪日期时间行为
• http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
• SQL Server 2008 和毫秒
• http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspx
• http://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx

回答by Tom

Almost all the Answers and Comments have been heavy on the Pros and light on the Cons. Here's a recap of all Pros and Cons so far plus some crucial Cons (in #2 below) I've only seen mentioned once or not at all.

几乎所有的答案和评论都强调了优点，而强调了缺点。这是迄今为止所有优点和缺点的回顾，以及一些关键的缺点（在下面的 #2 中）我只见过一次或根本没有提到。

1. PROS:

1. 优点：

1.1. More ISO compliant (ISO 8601) (although I don't know how this comes into play in practice).

1.1. 更符合 ISO (ISO 8601)（虽然我不知道这在实践中是如何发挥作用的）。

1.2. More range (1/1/0001 to 12/31/9999 vs. 1/1/1753-12/31/9999) (although the extra range, all prior to year 1753, will likely not be used except for ex., in historical, astronomical, geologic, etc. apps).

1.2. 更多的范围（1/1/0001 到 12/31/9999 vs. 1/1/1753-12/31/9999）（尽管额外的范围，都在 1753 年之前，可能不会被使用，除了例如，在历史、天文、地质等应用程序中）。

1.3. Exactly matches the range of .NET's DateTimeType's range (although both convert back and forth with no special coding if values are within the target type's range and precision except for Con # 2.1 below else error / rounding will occur).

1.3. 完全匹配 .NET 的DateTime类型范围的范围（尽管如果值在目标类型的范围和精度内，除了下面的 Con # 2.1 否则会发生错误/四舍五入时，两者都没有特殊编码来回转换）。

1.4. More precision (100 nanosecond aka 0.000,000,1 sec. vs. 3.33 millisecond aka 0.003,33 sec.) (although the extra precision will likely not be used except for ex., in engineering / scientific apps).

1.4. 更高的精度（100 纳秒又名 0.000,000,1 秒 vs. 3.33 毫秒，又名 0.003,33 秒）（尽管可能不会使用额外的精度，除非在工程/科学应用中除外）。

1.5. When configured for similar(as in 1 millisec not "same" (as in 3.33 millisec) as Iman Abidi has claimed) precision as DateTime, uses less space (7 vs. 8 bytes), but then of course, you'd be losing the precision benefit which is likely one of the two (the other being range) most touted albeit likely unneeded benefits).

1.5. 当配置为类似（如 Iman Abidi 声称的 1 毫秒不“相同”（如 3.33 毫秒））精度为 时DateTime，使用更少的空间（7 对 8 字节），但当然，你会失去精确的好处，这可能是最受吹捧的两个（另一个是范围）之一，尽管可能是不需要的好处）。

1. CONS:

2. 缺点：

2.1. When passing a Parameter to a .NET SqlCommand, you must specify System.Data.SqlDbType.DateTime2if you may be passing a value outside the SQL Server DateTime's range and/or precision, because it defaults to System.Data.SqlDbType.DateTime.

2.1. 将 Parameter 传递给 .NET 时SqlCommand，您必须指定System.Data.SqlDbType.DateTime2是否可能传递超出 SQL ServerDateTime范围和/或精度的值，因为它默认为System.Data.SqlDbType.DateTime。

2.2. Cannot be implicitly / easily converted to a floating-point numeric (# of days since min date-time) value to do the following to / with it in SQL Server expressions using numeric values and operators:

2.2. 不能隐式/轻松转换为浮点数字（自最小日期时间以来的天数）值，以便在使用数字值和运算符的 SQL Server 表达式中对其执行以下操作：

2.2.1. add or subtract # of days or partial days. Note: Using DateAddFunction as a workaround is not trivial when you're needing to consider multiple if not all parts of the date-time.

2.2.1. 添加或减去天数或部分天数。注意：DateAdd当您需要考虑日期时间的多个部分（如果不是全部）时，使用函数作为解决方法并非易事。

2.2.2. take the difference between two date-times for purposes of “age” calculation. Note: You cannot simply use SQL Server's DateDiffFunction instead, because it does not compute ageas most people would expect in that if the two date-times happens to cross a calendar / clock date-time boundary of the units specified if even for a tiny fraction of that unit, it'll return the difference as 1 of that unit vs. 0. For example, the DateDiffin Day's of two date-times only 1 millisecond apart will return 1 vs. 0 (days) if those date-times are on different calendar days (i.e. “1999-12-31 23:59:59.9999999” and “2000-01-01 00:00:00.0000000”). The same 1 millisecond difference date-times if moved so that they don't cross a calendar day, will return a “DateDiff” in Day's of 0 (days).

2.2.2. 出于“年龄”计算的目的，取两个日期时间之间的差异。注意：您不能简单地使用 SQL Server 的DateDiff函数，因为它不会age像大多数人所期望的那样计算，如果两个日期时间恰好跨越指定单位的日历/时钟日期时间边界，即使是很小的一部分该单位的差值，它将返回该单位的 1 与 0 的差异。例如，如果这些日期时间为 1 毫秒，则两个日期时间的DateDiffinDay将返回 1 与 0（天）在不同的日历日（即“1999-12-31 23:59:59.9999999”和“2000-01-01 00:00:00.0000000”）。如果移动相同的 1 毫秒差异日期时间，以便它们不会跨越日历日，则将在Day0（天）中返回“DateDiff” 。

2.2.3. take the Avgof date-times (in an Aggregate Query) by simply converting to “Float” first and then back again to DateTime.

2.2.3. 取Avg通过简单地转换为“浮动”的第一和然后再返回到的日期时间（在聚合查询）DateTime。

NOTE: To convert DateTime2to a numeric, you have to do something like the following formula which still assumes your values are not less than the year 1970 (which means you're losing all of the extra range plus another 217 years. Note: You may not be able to simply adjust the formula to allow for extra range because you may run into numeric overflow issues.

注意：要转换DateTime2为数字，您必须执行类似于以下公式的操作，该公式仍然假设您的值不小于 1970 年（这意味着您将失去所有额外范围加上另外 217 年。注意：您可能不能简单地调整公式以允许额外的范围，因为您可能会遇到数字溢出问题。

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0– Source: “ https://siderite.dev/blog/how-to-translate-t-sql-datetime2-to.html“

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0– 来源：“ https://siderite.dev/blog/how-to-translate-t-sql-datetime2-to.html”

Of course, you could also Castto DateTimefirst (and if necessary back again to DateTime2), but you'd lose the precision and range (all prior to year 1753) benefits of DateTime2vs. DateTimewhich are prolly the 2 biggest and also at the same time prolly the 2 least likely needed which begs the question why use it when you lose the implicit / easy conversions to floating-point numeric (# of days) for addition / subtraction / "age" (vs. DateDiff) / Avgcalcs benefit which is a big one in my experience.

当然，你也可以Cast到DateTime第一（如果必要，再次回到DateTime2），但你会失去精度和范围（全年开放前1753）的利益DateTime2与DateTime它们prolly 2最大，也同时prolly最不可能需要的 2 个，这引出了一个问题，为什么当您失去隐式/轻松转换为浮点数字（天数）以进行加法/减法/“年龄”（vs. DateDiff）/Avg计算的好处时为什么要使用它，这是一个很大的好处在我的经验中。

Btw, the Avgof date-times is (or at least shouldbe) an important use case. a) Besides use in getting average duration when date-times (since a common base date-time) are used to represent duration (a common practice), b) it's also useful to get a dashboard-type statistic on what the average date-time is in the date-time column of a range / group of Rows. c) A standard (or at least shouldbe standard) ad-hoc Query to monitor / troubleshoot values in a Column that may not be valid ever / any longer and / or may need to be deprecated is to list for each value the occurrence count and (if available) the Min, Avgand Maxdate-time stamps associated with that value.

顺便说一句，Avg日期时间是（或至少应该是）一个重要的用例。a) 除了在日期时间（因为一个共同的基准日期时间）用于表示持续时间（一种常见做法）时用于获取平均持续时间，b）获取关于平均日期的仪表板类型统计数据也很有用 -时间在一个范围/一组行的日期时间列中。c) 标准（或至少应该是标准的）临时查询来监视/排除列中可能不再有效和/或可能需要弃用的值的问题是列出每个值的出现次数和（如果可用）与该值关联的Min,Avg和Max日期时间戳。

回答by Baodad

Here is an example that will show you the differences in storage size (bytes) and precision between smalldatetime, datetime, datetime2(0), and datetime2(7):

下面是一个示例，它将向您展示 smalldatetime、datetime、datetime2(0) 和 datetime2(7) 在存储大小（字节）和精度方面的差异：

DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp

which returns

返回

sdt                  sdt_bytes  dt                       dt_bytes  dt20                 dt20_bytes  dt27                         dt27_bytes
2015-09-11 11:26:00  4          2015-09-11 11:25:42.417  8         2015-09-11 11:25:42  6           2015-09-11 11:25:42.4170000  8

So if I want to store information down to the second - but not to the millisecond - I can save 2 bytes each if I use datetime2(0) instead of datetime or datetime2(7).

因此，如果我想将信息存储到秒 - 但不是毫秒 - 如果我使用 datetime2(0) 而不是 datetime 或 datetime2(7)，我可以节省 2 个字节。

回答by Rhett A Brown

DateTime2 wreaks havoc if you are an Access developer trying to write Now() to the field in question. Just did an Access -> SQL 2008 R2 migration and it put all the datetime fields in as DateTime2. Appending a record with Now() as the value bombed out. It was okay on 1/1/2012 2:53:04 PM, but not on 1/10/2012 2:53:04 PM.

如果您是尝试将 Now() 写入相关字段的 Access 开发人员，则 DateTime2 会造成严重破坏。刚刚做了一个 Access -> SQL 2008 R2 迁移，它把所有的日期时间字段作为 DateTime2。使用 Now() 附加一条记录作为值被炸毁。2012 年 1 月 1 日下午 2:53:04 没问题，但 2012 年 1 月 10 日下午 2:53:04 不行。

Once character made the difference. Hope it helps somebody.

一旦性格有所不同。希望它可以帮助某人。

回答by FistOfFury

while there is increased precisionwith datetime2, some clients doesn't support date, time, or datetime2and force you to convert to a string literal. Specifically Microsoft mentions "down level" ODBC, OLE DB, JDBC, and SqlClient issues with these data types and has a chartshowing how each can map the type.

而有增加的精度与DATETIME2，某些客户端不支持日期，时间，或DATETIME2和强迫你转换为一个字符串。具体而言，Microsoft 提到了这些数据类型的“下级”ODBC、OLE DB、JDBC 和 SqlClient 问题，并有一个图表显示了每个类型如何映射类型。

If value compatabilityover precision, use datetime

如果值兼容性超过精度，请使用日期时间

回答by MJ Khan

Old Question... But I want to add something not already stated by anyone here... (Note: This is my own observation, so don't ask for any reference)

老问题……但我想补充一些这里没有人说过的东西……（注：这是我自己的观察，所以不要要求任何参考）

Datetime2 is faster when used in filter criteria.

Datetime2 在过滤条件中使用时更快。

TLDR:

域名注册地址：

In SQL 2016 I had a table with hundred thousand rows and a datetime column ENTRY_TIME because it was required to store the exact time up to seconds. While executing a complex query with many joins and a sub query, when I used where clause as:

在 SQL 2016 中，我有一个包含十万行和一个日期时间列 ENTRY_TIME 的表，因为它需要存储精确到秒的时间。在执行具有许多连接和子查询的复杂查询时，当我使用 where 子句时：

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

The query was fine initially when there were hundreds of rows, but when number of rows increased, the query started to give this error:

当有数百行时，查询最初很好，但是当行数增加时，查询开始出现此错误：

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

I removed the where clause, and unexpectedly, the query was run in 1 sec, although now ALL rows for all dates were fetched. I run the inner query with where clause, and it took 85 seconds, and without where clause it took 0.01 secs.

我删除了 where 子句，出乎意料的是，查询在 1 秒内运行，尽管现在获取了所有日期的所有行。我使用 where 子句运行内部查询，耗时 85 秒，没有 where 子句则耗时 0.01 秒。

I came across many threads here for this issue as datetime filtering performance

由于日期时间过滤性能，我在这里遇到了许多针对此问题的线程

I optimized query a bit. But the real speed I got was by changing the datetime column to datetime2.

我稍微优化了查询。但是我获得的真正速度是将日期时间列更改为 datetime2。

Now the same query that timed out previously takes less than a second.

现在，之前超时的相同查询只需不到一秒钟。

cheers

干杯

回答by Richard Fawcett

Interpretation of date strings into datetimeand datetime2can be different too, when using non-US DATEFORMATsettings. E.g.

日期字符串成的解释datetime和datetime2可以使用非美国时也是不同的，DATEFORMAT设置。例如

set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2

This returns 2013-05-06(i.e. May 6) for datetime, and 2013-06-05(i.e. June 5) for datetime2. However, with dateformatset to mdy, both @dand @d2return 2013-06-05.

这将返回2013-05-06（即 5 月 6 日）用于datetime和2013-06-05（即 6 月 5 日）用于datetime2。然而，随着dateformat集来mdy，无论是@d和@d2回报2013-06-05。

The datetimebehavior seems at odds with the MSDN documentationof SET DATEFORMATwhich states: Some character strings formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting. Obviously not true!

该datetime行为似乎与MSDN 文档不一致，SET DATEFORMAT其中指出：某些字符串格式，例如 ISO 8601，独立于 DATEFORMAT 设置进行解释。显然不是真的！

Until I was bitten by this, I'd always thought that yyyy-mm-dddates would just be handled right, regardless of the language / locale settings.

在我被这件事咬到之前，我一直认为yyyy-mm-dd日期会被正确处理，无论语言/区域设置如何。