SQL服务器中的DateTime2 vs DateTime

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

set dateformat dmydeclare @d datetime, @d2 datetime2select @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日期会被正确处理，不管语言/语言环境设置如何。

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

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

如果值兼容性大于精度，则使用datetime

根据这篇文章，如果您想使用DateTime2获得相同的DateTime精度，您只需使用DateTime2（3）。这应该会给您相同的精度，占用更少的字节，并提供扩展的范围。

下面是一个示例，它将向您展示在存储大小（字节）和精度之间的差异，其中包括：smalldatetime、datetime、datetime2（0）和datetime2（7）：

DECLARE @temp TABLE (sdt smalldatetime,dt datetime,dt20 datetime2(0),dt27 datetime2(7))
INSERT @tempSELECT 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个字节。

Select ValidUntil + 1from Documents

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

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

“让我们使用这种比旧的更糟糕的新类型”，我不这么认为！

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

1. 优点：

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

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

1.3.完全匹配. NET的DateTime Type的范围（尽管如果值在目标类型的范围内并且精度（Con#2.1以下除外），则无需特殊编码即可来回转换，否则将发生错误/舍入）。

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

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

2. 缺点：

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

2.2.不能隐式/轻松地转换为浮点数字（自min date-time以来的天数）值，以便在使用数值和运算符的SQLServer表达式中对其执行以下操作：

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

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

2.2.3.获取日期时间的Avg（在聚合查询中），只需先转换为“Float”，然后再返回到DateTime。

注意：要将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和DateTime的精度和范围（都在1753年之前）的好处，这可能是2个最大的好处，同时也可能是2个最不可能需要的好处，这就引出了一个问题，当你失去了隐式/容易转换为浮点数字（天数）的加法/减法/“年龄”（与DateDiff）/Avg计算的好处时，为什么要使用它。

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

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

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

Datetime2在筛选条件中使用时更快。

TLDR：

在2016年SQL，我有一个包含十万行和一个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 priorto completion of the operation or the server is not responding.

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

我在这个问题上遇到了许多线程日期时间滤波性能

我稍微优化了一下查询。但我获得的真正速度是通过将datetime列更改为datetime2。

现在，之前超时的相同查询花费的时间不到一秒。

欢呼

接受的答案是伟大的，只要知道，如果你发送一个DateTime2前端-它会四舍五入到正常的DateTime等效。

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

正如其他答案所示，由于更小的尺寸和更高的精度，建议使用datetime2，但这里有一些关于为什么不使用尼古拉·伊利奇的datetime2的想法：

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

datetime2更好

• datetime范围：1753-01-01到9999-12-31，datetime2范围：0001-01-01到9999-12-31

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

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

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