SQL Server 中的 DateTime2 与 DateTime

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

对新工作使用时间、日期、日期时间 2 和日期时间偏移数据类型。这些类型符合 SQL 标准。它们更便携。 time、datetime2 和 datetimeoffset 提供更高的秒精度。 datetimeoffset 为全球部署的应用程序提供时区支持。

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

---

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

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

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

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

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

---

datetime2 在大多数方面都胜出，除了（旧应用兼容性）

更大的值范围更好的精度更小的存储空间（如果指定了可选的用户指定精度）

https://i.stack.imgur.com/hNRsj.png

请注意以下几点

语法 datetime2[(小数秒精度=> 看下面的存储大小)]

datetime2[(小数秒精度=> 看下面的存储大小)]

精度，刻度为 0 到 7 位，精度为 100ns。默认精度为 7 位。

0到7位，精度100ns。

默认精度为 7 位。

存储大小 6 字节，精度小于 3；精度 3 和 4 需要 7 个字节。所有其他精度需要 8 个字节。

精度小于 3 时为 6 个字节；

精度 3 和 4 为 7 个字节。

所有其他精度需要 8 个字节。

DateTime2(3) 的位数与 DateTime 相同，但使用 7 个字节的存储空间而不是 8 个字节（SQLHINTS- DateTime Vs DateTime2）

在 datetime2（Transact-SQL MSDN 文章）上查找更多信息

图片来源：MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - Implementation and Maintenance 第 3 章：表格 ->第 1 课：创建表格 ->第 66 页

---

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

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

.NET 版本的 Datetime 具有与 DateTime2 相似的范围和精度。将 .net Datetime 映射到旧 SQL DateTime 时，会发生隐式舍入。旧的 SQL DateTime 精确到 3 毫秒。这意味着 11:59:59.997 离一天结束的时间很近。任何更高的值都将向上舍入到第二天。

尝试这个 ：

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'

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

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

---

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

优点：

1.1。更符合 ISO（ISO 8601）（尽管我不知道这在实践中是如何发挥作用的）。

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

1.3.与 .NET 的 DateTime 类型的范围完全匹配（尽管如果值在目标类型的范围和精度内，则在没有特殊编码的情况下来回转换，但下面的 Con # 2.1 除外，否则将发生错误/舍入）。

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

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

缺点：

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

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

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

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

2.2.3。通过简单地先转换为“Float”然后再转换回 DateTime 来获取日期时间的 Avg（在聚合查询中）。

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

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”

当然，您也可以先从 Cast 到 DateTime（如有必要再回到 DateTime2），但您会失去 DateTime2 与 { 2} 这是最大的 2 个，同时也是最不可能需要的 2 个，这就引出了一个问题，当您失去对浮点数字（天数）的隐式/简单转换以进行加法/减法 / 时，为什么要使用它“年龄”（相对于 DateDiff）/Avg 计算收益，在我的经验中这是一个很大的收益。

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

---

下面是一个示例，它将向您展示 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

返回

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

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

---

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

一旦性格产生了影响。希望它可以帮助某人。

---

使用非美国 DATEFORMAT 设置时，将日期字符串解释为 datetime 和 datetime2 也可能不同。例如

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

这将为 datetime 返回 2013-05-06（即 5 月 6 日），为 datetime2 返回 2013-06-05（即 6 月 5 日）。但是，如果将 dateformat 设置为 mdy，则 @d 和 @d2 都会返回 2013-06-05。

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

在我被这个咬住之前，我一直认为 yyyy-mm-dd 日期会得到正确处理，而不管语言/区域设置如何。

---

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

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

TLDR：

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

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

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

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

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

我在这里遇到了很多关于这个问题的主题datetime filtering performance

我对查询进行了一些优化。但我得到的真正速度是通过将 datetime 列更改为 datetime2。

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

干杯

---

虽然 datetime2 提高了精度，但某些客户端不支持 date、time 或 datetime2并强制您转换为字符串文字。具体来说，Microsoft 提到了与这些数据类型有关的“下层”ODBC、OLE DB、JDBC 和 SqlClient 问题，并有一个 chart 显示每个如何映射类型。

如果 兼容性 超过精度，请使用 datetime

---

根据 this article，如果您想使用 DateTime2 获得与 DateTime 相同的精度，您只需使用 DateTime2(3)。这应该为您提供相同的精度，占用更少的字节，并提供扩展的范围。

---

我刚刚偶然发现 DATETIME2 的另一个优势：它避免了 Python adodbapi 模块中的一个错误，如果传递了一个标准库 datetime 值，该值对于 DATETIME 列具有非零微秒但如果列定义为 DATETIME2，则工作正常。

---

正如其他答案所示，由于尺寸更小且精度更高，建议使用 datetime2，但这里有一些关于 why NOT to use datetime2 from Nikola Ilic 的想法：

缺乏（简单）对日期进行基本数学运算的可能性，例如 GETDATE()+1

每次与 DATEADD 或 DATEDIFF 进行比较时，都会完成到日期时间的隐式数据转换

SQL Server 无法正确使用 Datetime2 列的统计信息，因为存储数据的方式会导致非最佳查询计划，从而降低性能

---

我认为 DATETIME2 是存储 date 的更好方法，因为它比 DATETIME 具有更高的效率。在 SQL Server 2008 中您可以使用 DATETIME2，它存储日期和时间，需要 6-8 个 bytes 来存储，精度为 100 nanoseconds。因此，任何需要更高时间精度的人都需要 DATETIME2。

---

接受的答案很好，只要知道如果您将 DateTime2 发送到前端 - 它会四舍五入到正常的 DateTime 等效值。

这给我带来了一个问题，因为在我的解决方案中，我必须将发送的内容与重新提交时数据库中的内容进行比较，而我的简单比较“==”不允许四舍五入。所以必须加进去。

---

Select ValidUntil + 1
from Documents

上述 SQL 不适用于 DateTime2 字段。它返回错误“操作数类型冲突：datetime2 与 int 不兼容”

加 1 得到第二天是开发人员多年来一直在做的事情。现在微软有一个超级新的 datetime2 字段不能处理这个简单的功能。

“就用这种比旧款还差的新型吧”，我不这么认为！

---