SQL Server 中的 DateTime2 与 DateTime

Question

哪一个：

• 日期时间
• datetime2

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

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

Answer 1

datetime 的 MSDN 文档建议使用 日期时间2。这是他们的建议：

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

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

Answer 2

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。这就是这些类型的用途！

Answer 3

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

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

请注意以下几点

• 语法
  • datetime2[（小数秒精度=>查看存储大小）]
• 精度、小数位数
  • 0～7位，精度100ns。
  • 默认精度为 7 位数字。
• 存储大小
  • 精度小于 3 时为 6 个字节；
  • 精度 3 和 4 为 7 个字节。
  • 所有其他精度需要 8 个字节。
• DateTime2(3) 与 DateTime 具有相同的位数，但使用 7 字节存储而不是 8 字节 (SQLHINTS- DateTime 与 DateTime2)
• 有关详细信息，请参阅 datetime2（Transact-SQL MSDN 文章）

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

Answer 4

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

但是，讨论忽略了一件事......
@Marc_s 指出：两种类型都映射到 .NET 中的 System.DateTime - 没有区别。这是正确的，但是，反之则不然...并且在进行日期范围搜索时很重要（例如“查找 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-毫秒！ .aspx

Answer 5

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

1. 优点：

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 秒 vs. 3.33 毫秒又名 0.003,33 秒）（尽管除了工程/科学应用程序之外，可能不会使用额外的精度）。

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

2. 缺点：

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

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

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

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

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

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

。 , @Time) + DATEPART(纳秒, @Time) / 1.0E + 9) / 86400.0 – 来源：“ https://siderite.dev/blog/how-to-translate-t-sql-datetime2-to.html “

当然，你可以还首先将Cast转换为DateTime（如果有必要，再次返回DateTime2），但是您会丢失精度和范围（全部在年份之前） [第 1753 章]隐式/简单转换为浮点数字（天数）以进行加法/减法/“年龄”（与年龄） DateDiff) / Avg 计算的好处，根据我的经验，这是一个很大的好处。

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

Answer 6

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

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

Answer 7

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

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

Answer 8

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

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 秒。

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

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

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

干杯

Answer 9

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

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

对于日期时间，返回2013-05-06（即5月6日），对于2013-06-05（即6月5日）代码>日期时间2。但是，将 dateformat 设置为 mdy 时，@d 和 @d2 都会返回 2013-06- 05。

datetime 行为似乎与 SET DATEFORMAT 的“noreferrer">MSDN 文档其中指出：某些字符串格式（例如 ISO 8601）的解释与 DATEFORMAT 设置无关。显然不是真的！

在我被这个问题困扰之前，我一直认为无论语言/区域设置如何，yyyy-mm-dd 日期都会得到正确处理。

Answer 10

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

如果重视兼容性而不是精度，请使用日期时间

Answer 11

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

Answer 12

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

Answer 13

正如其他答案所示，由于尺寸更小且精度更高，建议使用 datetime2 ，但这里有一些关于 为什么不使用 Nikola Ilic 的 datetime2：

• 缺乏（简单）对日期进行基本数学运算的可能性，例如 GETDATE()+ 1
• 每次与 DATEADD 或 DATEDIFF 进行比较时，您都将完成与 datetime
• SQL Server 的隐式数据转换。不能对 Datetime2 列正确使用统计信息，因为数据存储方式会导致非最佳查询计划，从而降低性能

Answer 14

我认为 DATETIME2 是存储 date 的更好方法，因为它比
日期时间。在SQL Server 2008中，您可以使用DATETIME2，它存储日期和时间，需要6-8字节来存储，精度为<代码>100纳秒。因此，任何需要更高时间精度的人都会需要 DATETIME2。

Answer 15

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

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

Answer 16

datetime2 更好

• 日期时间范围：1753-01-01 到 9999-12-31，datetime2 范围：0001-01-01 到 9999-12-31

• datetime 精度：0.00333 秒，datetime2 精度：100 纳秒

• datetime 获取 8 个字节，datetime2 获取 6 到 8 个字节取决于精度

  （精度小于 3 时需要 6 个字节，精度 3 或 4 时需要 7 个字节，其他精度需要 8 个字节，请点击查看下图）

Answer 17

Select ValidUntil + 1
from Documents

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

多年来，开发人员一直在对日期进行加 1 以获得第二天。现在微软有一个超级新的 datetime2 字段，无法处理这个简单的功能。

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