SQL Server中的DateTime2 vs DateTime

#1 楼

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


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


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

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

#3 楼

datetime2在大多数方面都具有优势，但（与旧应用程序兼容）


更大的值范围
更好的精度

更小的存储空间（如果用户指定了可选功能）




请注意以下几点


语法


> datetime2 [（分数秒精度=>低于存储大小）]


精度，小数位数


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


存储大小


6个字节表示小于3的精度；
7位字节用于精度3和4。
所有其他精度要求8个字节。



DateTime2（3）具有与位数相同的位数。 DateTime，但使用7个字节的存储空间，而不是8个字节（SQLHINTS- DateTime与DateTime2）
有关datetime2的更多信息（Transact-SQL MSDN文章）


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

#4 楼

我同意@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毫秒！.aspx

#5 楼

几乎所有的答案和评论都对赞成者表示沉重而对缺点表示反对。这是到目前为止所有优点和缺点的概述，以及一些关键的缺点（在下面的＃2中），我只看过一次或根本没有提到过。


PROS：

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

1.2。更大的范围（1/1/0001到12/31/9999与1/1 / 1753-12 / 31/9999）（尽管除了ex。，在历史，天文，地质等应用中）。

DateTime类型范围的范围（尽管值在目标类型的范围和精度之内（除非下面的Con＃2.1除外，否则两者都可以使用特殊编码来回转换）。

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

1.5。如果将其配置为与DateTime相似（与1manisec不同，而不是Iman Abidi所称的“ 3.33 millisec”），则占用的空间更少（7个字节与8个字节），但是当然，您会丢失精确度好处，尽管可能是不必要的好处，但它可能是最受吹捧的两个好处之一（另一个是范围）。


缺点：

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

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

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

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

2.2.3。首先简单地转换为“浮点数”，然后再次返回到Avg，以获取日期时间的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年之前），这是q212079q的最大优势，也是同时产生最少的两个需求，这就是当您失去对加/减/“年龄”（与DateDiff）/ Avg进行的隐式/轻松转换为浮点数字（天数）的隐式/简易转换时为什么使用它的问题calcs收益对我来说是一个很大的经验。

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

#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

因此，如果我想将信息存储到第二秒（而不是毫秒），如果我使用datetime2（0）而不是datetime或datetime2（7），则每个字节可以节省2个字节。

#7 楼

如果您是Access开发人员，试图将Now（）写入相关字段，则DateTime2将造成严重破坏。只需执行Access-> SQL 2008 R2迁移，它将所有datetime字段都放入DateTime2中。用Now（）追加一条记录，因为该值被炸掉了。在1/1/2012 2:53:04 PM可以，但在1/10/2012 2:53:04 PM可以。

角色一旦有所不同。希望对别人有帮助。

#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列更改为datetime2。

现在以前超时的同一查询只需不到一秒钟的时间。

欢呼声

#9 楼

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

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

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

datetime的行为似乎与SET DATEFORMAT的MSDN文档不一致，该文档指出：某些字符串格式（例如ISO 8601）被解释与DATEFORMAT设置无关。显然不是真的！

直到我被这咬伤为止，我一直以为yyyy-mm-dd日期将被正确处理，而不管语言/区域设置如何。

#10 楼

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

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

#11 楼

根据本文的介绍，如果您希望使用DateTime2获得与DateTime相同的精度，则只需使用DateTime2（3）。这应该给您相同的精度，减少一个字节，并提供更大的范围。

#12 楼

我只是偶然发现了DATETIME2的另一个优点：它避免了Python adodbapi模块中的错误，如果传递了标准库datetime值（对于DATETIME列具有非零微秒），则该错误会被炸毁，但是如果将该列定义为DATETIME2。

#13 楼

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

#14 楼

Select ValidUntil + 1
from Documents

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

加1开发第二天是开发人员多年来一直在做的事情。现在，Microsoft有一个超级新的datetime2字段，它无法处理此简单功能。

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