SQL Server中的DateTime2与DateTime有何区别？

Question

哪一个：

• datetime
• datetime2

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

我知道它们在精度（和存储空间可能）上有所不同，但暂时忽略这些差异，是否有最佳实践文件来指导何时使用什么，或者我们应该只使用 datetime2？

- Mikeon

Answer 1

MSDN文档建议使用datetime2。以下是他们的建议：

使用time、date、datetime2和datetimeoffset数据类型进行新的工作。这些类型符合SQL标准，更具可移植性。time、datetime2和datetimeoffset提供更多的秒精度。datetimeoffset为全球部署应用程序提供时区支持。

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

- Adam Porad

---

尽管datetime2更加精确，但有些客户端不支持日期、时间或datetime2，并会强制将其转换为字符串。如果你更关注兼容性而非精度，建议使用datetime。 - FistOfFury

另一个选项是使用索引视图，将该列转换为日期时间以实现兼容性。但是您需要能够将应用程序指向该视图。 - TamusJRoyce

使用DATETIMEOFFSET的时区支持是一个误称，它只存储特定时间点的UTC偏移量，而不是时区。 - Suncat2000

@Porad：由于“SQL标准”的可移植性更强，实际上有什么好处呢？除了使你编写显著更多、可读性/可维护性显著较低的代码以便移植到其他关系型数据库管理系统（在该代码的生命周期内可能永远不会发生），还有什么好处呢？除了可能微软提供的SQL Server工具和驱动程序之外，是否有任何应用程序实际上依赖于“DateTime2”类型的特定位级表示（或任何其他SQL Server类型）？请参阅我下面于2017年7月10日的回答中的缺点，了解我为什么要这样问。 - Tom

@Adam Porad: 此外，所有这些好处很可能是不必要的（除了工程或科学应用程序），因此不值得失去更重要的好处：更容易（即使考虑变通方法）将其隐式/显式转换为浮点数数字（包括天数在内，分数天从最小日期时间开始），以进行加法、减法、最小值、最大值和平均值计算。有关详细信息，请参见我的7/10/17答案中的缺点。 - Tom

Answer 2

DATETIME2的日期范围是从"0001/01/01"到"9999/12/31"，而DATETIME类型只支持1753年至9999年。

此外，如果需要的话，DATETIME2在时间上可以更精确；DATETIME只能精确到3 1/3毫秒，而DATETIME2可以精确到100纳秒。

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

如果可以选择，我建议尽可能使用DATETIME2。我没有看到使用DATETIME的任何好处（除了向后兼容性）- 这样你就不会遇到日期超出范围等问题。

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

- marc_s

---

当将.NET DateTime值作为参数添加到SqlCommand时要小心，因为它可能会假设它是旧的datetime类型，并且如果您尝试写入1753-9999年范围外的DateTime值，除非您显式指定类型为System.Data.SqlDbType.DateTime2的SqlParameter，否则会出现错误。无论如何，datetime2很棒，因为它可以存储.NET DateTime类型可存储的任何值。 - Triynko

@marc_s - 这不是null的作用吗？ - JohnFx

@JohnFX - 有点晚了 - 但你不会将日期时间设为null。相反，你应该使用Nullable<datetime>或datetime？来处理null值，映射到存储过程时，只需执行param.value = someDateTime ?? DBValue.Null即可。很遗憾我们被困在一种带有数字后缀的数据类型中 - 这似乎太“通用”了:) - Adam Tuliper

刚才我试图给自己的评论点赞（在上面那个），后来才意识到这是我一年前发表的评论。我仍在处理.NET框架的愚蠢设计决策，即默认情况下通过SqlParameters传递所有DateTime值时将其截断，除非你显式地将其设置为更精确的SqlDbType.DateTime2。太遗憾了，无法自动推断正确的类型。实际上，他们应该使更改对用户透明，替换不够精确、效率低、范围有限的实现，并保留原始的“datetime”类型名称。另请参见https://dev59.com/1F7Va4cB1Zd3GeqPGA6z。 - Triynko

Nullable<DateTime> 不就是为了这个吗？ - ChrisW

Answer 3

datetime2在大多数方面都胜过(旧应用兼容性方面的不足)

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

请注意以下几点

• 语法
  • datetime2 [（分数秒精度=>参见下面的存储大小）]
• 精度，刻度
  • 0到7位数字，精度为100纳秒。
  • 默认精度为7位数字。
• 存储大小
  • 精度小于3需要6个字节；
  • 精度为3和4时需要7个字节。
  • 所有其他精度需要8个字节。
• DateTime2(3)与DateTime具有相同数量的数字，但使用7个字节的存储空间而不是8个字节(SQLHINTS- DateTime Vs DateTime2)
• 查看更多信息：datetime2(Transact-SQL MSDN article)

图片来源： MCTS自学教程（考试70-432）：Microsoft® SQL Server® 2008实现和维护 第3章：表 -> 第1课：创建表 -> 第66页

- Iman

---

感谢您展示了统计数据加1的效果，datetime2 真是太棒了（获胜者）。 - Pankaj Parkar

根据你提到的“SQLHINTS- DateTime Vs DateTime2”文章中Oskar Berggren于2014年9月10日下午3:51发表的评论，datetime2（3）与datetime不同。“它们将具有相同数量的数字，但datetime的精度为3.33毫秒，而datetime2（3）的精度为1毫秒。” - Tom

@PankajParkar：哇，不要这么快。你可能想看一下我在7/10/17下面回答的缺点部分。 - Tom

datetime2如何比datetime占用更少的存储空间，同时提供更大的范围和更高的精度？ - Dai

我遇到的一个问题是，对DATETIME2（7）取MAX（）会得到一个INT。因此，如果您将原始值与该列的MAX进行比较以获取一组行的结果，则在比较中仅获得分钟级别的精度，并且在尝试查找组中最新条目时不会获得预期的结果。那真是出乎意料。 - Mark Freeman

@Dai，我很确定答案已经解释了这个问题。如果你使用datetime2(3)，你可以为datetime节省空间。如果你声明更高的精度（在datetime中不是一个选项），你不会节省空间，但你会获得更高的精度。总之，简而言之，空间节省是为了相等的精度。 - ruffin

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-Milliseconds!.aspx

- EBarr

---

你可以通过避免试图寻找一天的“结束”来避免这种舍入。使用“>= May 5 AND < May 6”会更安全，而且适用于任何日期/时间类型（当然不包括TIME）。建议避免使用像m/d/yyyy这样的地区性、不明确的格式。 - Aaron Bertrand

@AaronBertrand - 完全同意，但是考虑到我们拥有这个问题的数量，描述它似乎是值得的。 - EBarr

你为什么从 20100505 改成了 5/5/2010？前者格式能在 SQL Server 的任何区域正常运行。后者将会出错：SET LANGUAGE French; SELECT Convert(datetime, '1/7/2015') 出错了：2015-07-01 00:00:00.000。 - ErikE

@EBarr：关于“DateTime2是向前推进的首选方法。它具有更广泛的日期范围、更高的精度，并且使用相等或更少的存储（取决于精度）”一文，我强烈反对。请参见我在下面7/10/17日期的回答中的缺点部分。简而言之，这些好处可能是不必要的（在工程/科学应用之外），因此不值得失去更有可能需要的好处，即更容易（即使考虑到解决方法）将其隐式/显式转换为浮点数值（包括应用程序中的天数、自最小日期时间以来的分数）进行+、-和平均值运算。 - Tom

Answer 5

几乎所有的回答和评论都强调了优点，而对缺点并未特别提及。以下是迄今为止所有优点和缺点的总结以及一些至关重要的缺点（在下面第2点中），我只见过一次或者根本没有提到。

1. 优点：

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

1.2 范围更大（1/1/0001到12/31/9999，与1/1/1753-12/31/9999相比）（尽管额外范围，即1753年之前的所有内容，可能不会被使用，除非在历史、天文、地质等应用程序中）。

1.3 正好匹配.NET的DateTime类型的范围（尽管两者互相转换不需要特殊编码，如果值在目标类型的范围和精度内，除了第2个缺点1之外，否则会出现错误/舍入）。

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

1.5 当配置为与DateTime类似的（例如1毫秒而不是Iman Abidi所声称的3.33毫秒）精度时，使用更少的空间（7 vs. 8字节），但当然，您将失去可能是受到赞扬的两个（另一个是范围）优点之一的精度优势，尽管这种优势可能是不必要的。

2. 缺点：

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。例如，在不同日历日期上（即“1999-12-31 23:59:59.9999999”和“2000-01-01 00:00:00.0000000”）只相差1毫秒的两个日期时间的DateDiff以天为单位将返回1而不是0（天）。如果移动相同的1毫秒差异日期时间，使其不跨越日历日，则DateDiff的天数为0（天）。

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），但这样就会失去精度和范围（1753 年之前的所有内容）与 DateTime 相比的好处，它们可能是两个最大的优点，同时也可能是最不可能需要的两个优点，这引出了一个问题：为什么要使用它，当您失去对浮点数数字（天数）进行加法/减法/"年龄"（与 DateDiff 不同）/ Avg 计算的隐式/简单转换的好处，这在我的经验中是一个很大的优势。

顺便说一下，Avg 的日期时间（或者至少应该是）一个重要的用例。a）除了用于获取平均持续时间之外，当日期时间（自一个共同的基础日期时间）用于表示持续时间时（这是一个常见的做法），b）它还有用于在一组行的日期时间列中获取仪表板类型统计的平均日期时间。c）监视/故障排除列中可能永远无效/不再有效并且/或需要弃用的值的标准（或至少应该是标准）即为列出每个值的发生计数和（如果可用）关联该值的Min、Avg和Max 日期时间戳。

- Tom

---

像反对者的观点一样 - 它指出了等式中C#的一面。结合所有其他“优点”，它将使人们能够根据他们想要解决的问题做出明智的选择。 - EBarr

@EBarr: 我的“反向观点”只有Cons #1部分指出了等式中C#的一面。其余部分（缺点#2.2.1 - 2.2.3）与SQL Server查询和语句的影响有关，正如我所说，这些是更可能需要的DateTime的好处。 - Tom

Re 2.2.1 -- 在编写日期计算时使用算术运算被认为是不安全的做法，最好的方法始终是使用DateAdd和相关函数。这是最佳实践。进行日期计算存在严重的责任风险，其中最小的一个是大多数日期类型无法正常工作。以下是一些文章：https://www.sqlservercentral.com/blogs/manipulating-dates-and-times-in-t-sql https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations - RBerman

关于“不安全”的问题：它只会在某些日期类型（比如我已经提到的 DateTime2 类型，由于溢出的可能性较高）下不安全。关于“对大多数日期类型都不起作用”的问题：你只需要它能够适用于其中一个日期类型，在大多数应用程序中，大多数日期类型可能永远不需要转换为另一种日期类型（除了可能像我之前提到的那样，从 DateTime2 转换为 DateTime（例如进行“日期算术” ;)）。考虑到这一点，在编写非算术友好型日期类型的额外代码以及针对性的研究查询方面，这并不值得。 - Tom

Answer 6

这里有一个示例，它将向您展示smalldatetime、datetime、datetime2(0)和datetime2(7)之间的存储大小（字节）和精度差异：

以下是示例中的查询:

CREATE TABLE #dateTable ( 
    SmallDateTimeCol SMALLDATETIME, 
    DateTimeCol DATETIME, 
    DateTime2_0Col DATETIME2(0), 
    DateTime2_7Col DATETIME2(7) 
); 

INSERT INTO #dateTable VALUES ('2015-01-01 12:12:12', '2015-01-01 12:12:12', '2015-01-01 12:12:12', '2015-01-01 12:12:12'); 

SELECT   
    CONVERT(VARCHAR(24),SmallDateTimeCol,121) AS Smalldatetime 
    ,CONVERT(VARCHAR(24),DateTimeCol,121) AS Datetime 
    ,CONVERT(VARCHAR(24),DateTime2_0Col,121) AS 'Datetime2(0)' 
    ,CONVERT(VARCHAR(24),DateTime2_7Col,121) AS 'Datetime2(7)' 
    ,DATALENGTH(SmallDateTimeCol) AS 'SmallDateTime_Size' 
    ,DATALENGTH(DateTimeCol) AS 'DateTime_Size' 
    ,DATALENGTH(DateTime2_0Col) AS 'DateTime2_0_Size' 
    ,DATALENGTH(DateTime2_7Col) AS 'DateTime2_7_Size' 
FROM #dateTable; 

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个字节。

- Baodad

Answer 7

如果你是Access开发人员，试图向相关字段写入Now()的话，DateTime2将会给你带来麻烦。我刚进行了一次Access -> SQL 2008 R2迁移，结果所有datetime字段都作为DateTime2放置。尝试使用Now()作为值添加记录时失败了。在2012年1月1日下午2:53:04是可以的，但在2012年1月10日下午2:53:04就不行了。

只有一个字符的区别。希望对某些人有所帮助。

- Rhett A Brown

Answer 8

关于一个老问题......但是我想补充一些其他人没有提到的内容......（注意：这是我的个人观察，所以不要要求任何参考资料）

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

TLDR：

在SQL 2016中，我有一个带有十万行和一个datetime列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过滤性能的帖子

我稍微优化了查询。但真正加快速度的是将datetime列更改为datetime2。

现在之前超时的相同查询只需要不到一秒就能完成。

干杯

- MJ Khan

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

对于datetime，它会返回2013-05-06（即5月6日）的值，而对于datetime2，则返回2013-06-05（即6月5日）。但是，如果将dateformat设置为mdy，那么@d和@d2都会返回2013-06-05。

datetime的行为似乎与SET DATEFORMAT的MSDN文档相矛盾，后者指出：某些字符格式（例如ISO 8601）将独立于DATEFORMAT设置进行解释。显然这并不正确！

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

- Richard Fawcett

---

不对。关于ISO 8601，我认为你是想说YYYYMMDD（没有破折号）。 SET LANGUAGE FRENCH; DECLARE @d DATETIME = '20130605'; SELECT @d; 请使用破折号再试一次。 - Aaron Bertrand

标准允许使用YYYY-MM-DD和YYYYMMDD两种格式来表示日历日期。我认为MSDN应该更明确地说明独立解释ISO 8601规范的哪个子集！ - Richard Fawcett

我知道，但在SQL Server中，只有无破折号语法是安全的。 - Aaron Bertrand

Answer 10

虽然使用 datetime2 数据类型能够提高精度，但是一些客户端可能不支持 date、time 或者 datetime2 数据类型，这时你需要将它们转换为字符串字面量。具体来说，Microsoft 提到了 "down level" ODBC、OLE DB、JDBC 和 SqlClient 与这些数据类型相关的问题，并提供了图表，展示了如何映射每种类型。

如果更看重值的兼容性而非精度，请使用 datetime 数据类型。

- FistOfFury