哪一个：
datetime
datetime2
是在SQL Server 2008中存储日期和时间的推荐方法吗？
我知道精度（和存储空间可能）的差异，但暂时忽略这些，是否有关于何时使用什么的最佳实践文档，或者我们应该只使用 datetime2 ？
根据this article，如果您希望使用DateTime2具有相同的DateTime精度你只需要使用DateTime2（3） . 这应该给你相同的精度，占用更少的字节，并提供扩展的范围 .
我只是偶然发现 DATETIME2 的另一个优势：它避免了Python adodbapi 模块中的一个错误，如果传递的标准库 datetime 值为 DATETIME 列具有非零微秒，则该错误会爆炸，但如果列定义为 DATETIME2 .
DATETIME2
adodbapi
DATETIME
datetime的MSDN文档建议使用datetime2 . 以下是他们的建议：
使用time，date，datetime2和datetimeoffset数据类型进行新工作 . 这些类型与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 - 没有区别 .
System.DateTime
如果您有选择，我建议尽可能使用 DATETIME2 . 我没有看到使用 DATETIME 的任何好处（向后兼容性除外） - 你会遇到更少的麻烦（日期超出范围并且麻烦这样） .
另外：如果您只需要日期（没有时间部分），请使用DATE - 它和 DATETIME2 一样好，也可以节省空间！ :-)同样只是时间 - 使用 TIME . 这就是这些类型的用途！
TIME
datetime2 在大多数方面获胜，但（旧应用程序兼容性）
更大 range of values
更好 Accuracy
较小 storage space （如果指定了可选的用户指定精度）
请注意以下几点
语法
datetime2 [（小数秒精度=>查看低于存储空间大小）]
精度，规模
0到7位数，精度为100ns .
默认精度为7位数 .
存储空间大小
6个字节，精度小于3;
7个字节用于精度3和4 .
所有其他精度 require 8 bytes .
DateTime2(3) 与DateTime具有相同的位数，但使用7个字节的存储而不是8个字节（SQLHINTS- DateTime Vs DateTime2）
在datetime2(Transact-SQL MSDN article)上查找更多信息
image source：MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - Implementation and Maintenance第3章：表 - >第1课：创建表 - >第66页
我同意@marc_s和@Adam_Poward - DateTime2是前进的首选方法 . 它具有更广泛的日期，更高的精度，并使用相同或更少的存储（取决于精度） .
One thing the discussion missed, however...@Marc_s声明： Both types map to System.DateTime in .NET - no difference there . 这是正确的， however, the inverse is not true ......在进行日期范围搜索时很重要（例如"find me all records modified on 5/5/2010"） .
Both types map to System.DateTime in .NET - no difference there
.NET的 Datetime 版本与 DateTime2 具有相似的范围和精度 . 将.net Datetime 映射到旧SQL DateTime 和 implicit rounding occurs 时 . 旧的SQL DateTime 精确到3毫秒 . 这意味着 11:59:59.997 尽可能接近当天结束 . 任何更高的值都会向上舍入到第二天 .
Datetime
DateTime2
DateTime
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的重要原因 . 隐含的日期舍入显然会引起混淆：
Strange datetime behavior in SQL Server
http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
SQL Server 2008 and milliseconds
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
如果你是一个尝试将Now（）写入相关字段的Access开发人员，DateTime2会造成严重破坏 . 刚刚进行了Access - > SQL 2008 R2迁移，它将所有日期时间字段都放在DateTime2中 . 使用Now（）追加记录作为被轰炸的值 . 这是在2012年1月1日下午2:53:04没关系，但不是在2012年1月10日下午2:53:04 .
一旦角色有所作为 . 希望它对某人有帮助 .
下面是一个示例，它将向您展示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个字节 .
几乎所有的答案和评论都是关于优点的重点和缺点 . 以下是迄今为止所有优点和缺点的回顾以及一些关键的缺点（下面的＃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 Type范围（尽管如果值在目标类型的范围和精度范围内，两者都来回转换，没有特殊编码除了Con＃2.1以下，否则将发生错误/舍入） .
1.4 . 更精确（100纳秒又名0.000,000,1秒与3.33毫秒又名0.003,33秒）（尽管除了ex . 之外，在工程/科学应用程序中可能不会使用额外的精度） .
1.5 . 当配置为类似（如1毫秒不是"same"（如3.33毫秒），如Iman Abidi所声称的）精度为 DateTime 时，使用较少的空间（7对8字节），但当然，你将失去精度虽然可能是不必要的好处，但可能是两个中的一个（另一个是范围）最受欢迎的好处 .
2.1 . 将参数传递给.NET SqlCommand 时，如果您传递的值超出SQL Server DateTime 的范围和/或精度，则必须指定 System.Data.SqlDbType.DateTime2 ，因为它默认为 System.Data.SqlDbType.DateTime .
SqlCommand
System.Data.SqlDbType.DateTime2
System.Data.SqlDbType.DateTime
2.2 . 无法使用数值和运算符在SQL Server表达式中隐式/轻松转换为浮点数字（自最小日期时间以来的天数）值，以便对其执行以下操作：
2.2.1 . 添加或减去天数或部分天数 . 注意：当您需要考虑日期时间的多个（如果不是全部）部分时，使用 DateAdd 函数作为变通方法并非易事 .
DateAdd
2.2.2 . 为了“年龄”计算，取两个日期时间之间的差异 . 注意：您不能简单地使用SQL Server的 DateDiff 函数，因为它不会像大多数人所期望的那样计算 age 如果两个日期时间恰好跨越指定单位的日历/时钟日期 - 时间边界，即使对于该单位的一小部分，它将返回差异为该单位的1与0相比 . 例如， DateDiff 在 Day 的两个日期时间间隔仅1毫秒将返回1对0（天）如果那些日期时间在不同的日历日（即“1999-12-31 23：59：59.9999999”和“2000-01-01 00：00：00.0000000”） . 相同的1毫秒差异日期 - 如果移动它们不跨越日历日，将在 Day 的0（天）中返回“DateDiff” .
DateDiff
age
Day
2.2.3 . 通过首先转换为“Float”然后再转换回 DateTime 来获取日期时间的 Avg （在聚合查询中） .
Avg
注意：要将 DateTime2 转换为数字，您必须执行以下公式，但仍假设您的值不低于1970年（这意味着您将失去所有额外范围再加上217年 . 注意：您可能无法简单地调整公式以允许额外的范围，因为您可能遇到数字溢出问题 .
25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 - 来源：“https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html”
25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0
当然，你也可以首先__33448_到 DateTime （如果有必要再回到 DateTime2 ），但是你会失去 DateTime2 与 DateTime 之间的精度和范围（前所未有的） DateTime 这两个最大的优势也是与此同时，最不可能需要的两个问题就是问题为什么当你失去隐式/简单转换为浮点数（天数）加/减/ "age"（对比 DateDiff ）/ Avg 计算效益时使用它的原因根据我的经验，这是一个很大的问题 .
顺便说一下， Avg 的日期时间是（或者至少应该是）一个重要的用例 . a）除了用于获得平均持续时间时，日期时间（因为共同的基准日期时间）用于表示持续时间（通常的做法），b）获得关于平均日期的仪表板类型统计数据也是有用的 - time位于范围/行组的日期时间列中 . c）标准（或至少应该是标准的）ad-hoc查询监视/排除列中可能无效/不再有效和/或可能需要弃用的值，是为每个值列出事件计数和（如果可用）与该值关联的 Min ， Avg 和 Max 日期时间戳 .
Min
Max
在使用非美国 DATEFORMAT 设置时，将日期字符串解释为 datetime 和 datetime2 也可能不同 . 例如 .
DATEFORMAT
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 .
2013-05-06
2013-06-05
dateformat
mdy
@d
@d2
datetime 行为与 SET DATEFORMAT 的 SET DATEFORMAT 似乎不一致，后者表示：某些字符串格式（例如ISO 8601）的解释与DATEFORMAT设置无关 . 显然不是真的！
SET DATEFORMAT
直到我被这个咬了，我总是认为只要处理正确的 yyyy-mm-dd 日期，无论语言/语言环境设置如何 .
yyyy-mm-dd
虽然 precision 与datetime2相比有所增加，但某些客户端不支持日期，时间或日期时间2并强制您转换为字符串文字 . 具体来说，Microsoft提到了这些数据类型的ODBC，OLE DB，JDBC和SqlClient问题，并且chart显示了每个数据类型的映射方式 .
如果值 compatability 超过精度，请使用datetime
旧问题......但是我想在这里添加一些尚未说明的东西...（注意：这是我自己的观察，所以不要求任何参考）
Datetime2 is faster when used in filter criteria.
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是存储日期的更好方法，因为它比DATETIME具有更高的效率 . 在SQL Server 2008中，您可以使用DATETIME2，它存储日期和时间，需要6-8个字节进行存储，精度为100纳秒 . 因此，任何需要更高时间精度的人都需要DATETIME2 .
Select ValidUntil + 1 from Documents
上述SQL不适用于DateTime2字段 . 它返回并出错“操作数类型冲突：datetime2与int不兼容”
添加1以获得第二天是开发人员多年来一直在做的事情 . 现在微软有一个超级新的datetime2字段，无法处理这个简单的功能 .
“让我们使用比旧款更糟糕的新型号”，我不这么认为！
14 回答
根据this article，如果您希望使用DateTime2具有相同的DateTime精度你只需要使用DateTime2（3） . 这应该给你相同的精度，占用更少的字节，并提供扩展的范围 .
我只是偶然发现
DATETIME2的另一个优势：它避免了Pythonadodbapi模块中的一个错误，如果传递的标准库datetime值为DATETIME列具有非零微秒，则该错误会爆炸，但如果列定义为DATETIME2.datetime的MSDN文档建议使用datetime2 . 以下是他们的建议：
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 在大多数方面获胜，但（旧应用程序兼容性）
更大 range of values
更好 Accuracy
较小 storage space （如果指定了可选的用户指定精度）
请注意以下几点
语法
datetime2 [（小数秒精度=>查看低于存储空间大小）]
精度，规模
0到7位数，精度为100ns .
默认精度为7位数 .
存储空间大小
6个字节，精度小于3;
7个字节用于精度3和4 .
所有其他精度 require 8 bytes .
DateTime2(3) 与DateTime具有相同的位数，但使用7个字节的存储而不是8个字节（SQLHINTS- DateTime Vs DateTime2）
在datetime2(Transact-SQL MSDN article)上查找更多信息
image source：MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - Implementation and Maintenance第3章：表 - >第1课：创建表 - >第66页
我同意@marc_s和@Adam_Poward - DateTime2是前进的首选方法 . 它具有更广泛的日期，更高的精度，并使用相同或更少的存储（取决于精度） .
One thing the discussion missed, however...
@Marc_s声明：
Both types map to System.DateTime in .NET - no difference there. 这是正确的， however, the inverse is not true ......在进行日期范围搜索时很重要（例如"find me all records modified on 5/5/2010"） ..NET的
Datetime版本与DateTime2具有相似的范围和精度 . 将.netDatetime映射到旧SQLDateTime和 implicit rounding occurs 时 . 旧的SQLDateTime精确到3毫秒 . 这意味着11:59:59.997尽可能接近当天结束 . 任何更高的值都会向上舍入到第二天 .试试这个 ：
避免这种隐式舍入是转移到DateTime2的重要原因 . 隐含的日期舍入显然会引起混淆：
Strange datetime behavior in SQL Server
http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
SQL Server 2008 and milliseconds
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
如果你是一个尝试将Now（）写入相关字段的Access开发人员，DateTime2会造成严重破坏 . 刚刚进行了Access - > SQL 2008 R2迁移，它将所有日期时间字段都放在DateTime2中 . 使用Now（）追加记录作为被轰炸的值 . 这是在2012年1月1日下午2:53:04没关系，但不是在2012年1月10日下午2:53:04 .
一旦角色有所作为 . 希望它对某人有帮助 .
下面是一个示例，它将向您展示smalldatetime，datetime，datetime2（0）和datetime2（7）之间存储大小（字节）和精度的差异：
返回
因此，如果我想将信息存储到第二个 - 但不是毫秒 - 如果我使用datetime2（0）而不是datetime或datetime2（7），我可以每个节省2个字节 .
几乎所有的答案和评论都是关于优点的重点和缺点 . 以下是迄今为止所有优点和缺点的回顾以及一些关键的缺点（下面的＃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的
DateTimeType范围（尽管如果值在目标类型的范围和精度范围内，两者都来回转换，没有特殊编码除了Con＃2.1以下，否则将发生错误/舍入） .1.4 . 更精确（100纳秒又名0.000,000,1秒与3.33毫秒又名0.003,33秒）（尽管除了ex . 之外，在工程/科学应用程序中可能不会使用额外的精度） .
1.5 . 当配置为类似（如1毫秒不是"same"（如3.33毫秒），如Iman Abidi所声称的）精度为
DateTime时，使用较少的空间（7对8字节），但当然，你将失去精度虽然可能是不必要的好处，但可能是两个中的一个（另一个是范围）最受欢迎的好处 .2.1 . 将参数传递给.NET
SqlCommand时，如果您传递的值超出SQL ServerDateTime的范围和/或精度，则必须指定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相比 . 例如，DateDiff在Day的两个日期时间间隔仅1毫秒将返回1对0（天）如果那些日期时间在不同的日历日（即“1999-12-31 23：59：59.9999999”和“2000-01-01 00：00：00.0000000”） . 相同的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.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html”当然，你也可以首先__33448_到
DateTime（如果有必要再回到DateTime2），但是你会失去DateTime2与DateTime之间的精度和范围（前所未有的）DateTime这两个最大的优势也是与此同时，最不可能需要的两个问题就是问题为什么当你失去隐式/简单转换为浮点数（天数）加/减/ "age"（对比DateDiff）/Avg计算效益时使用它的原因根据我的经验，这是一个很大的问题 .顺便说一下，
Avg的日期时间是（或者至少应该是）一个重要的用例 . a）除了用于获得平均持续时间时，日期时间（因为共同的基准日期时间）用于表示持续时间（通常的做法），b）获得关于平均日期的仪表板类型统计数据也是有用的 - time位于范围/行组的日期时间列中 . c）标准（或至少应该是标准的）ad-hoc查询监视/排除列中可能无效/不再有效和/或可能需要弃用的值，是为每个值列出事件计数和（如果可用）与该值关联的Min，Avg和Max日期时间戳 .在使用非美国
DATEFORMAT设置时，将日期字符串解释为datetime和datetime2也可能不同 . 例如 .这为
datetime返回2013-05-06（即5月6日），为datetime2返回2013-06-05（即6月5日） . 但是，将dateformat设置为mdy，@d和@d2都返回2013-06-05.datetime行为与SET DATEFORMAT的SET DATEFORMAT似乎不一致，后者表示：某些字符串格式（例如ISO 8601）的解释与DATEFORMAT设置无关 . 显然不是真的！直到我被这个咬了，我总是认为只要处理正确的
yyyy-mm-dd日期，无论语言/语言环境设置如何 .虽然 precision 与datetime2相比有所增加，但某些客户端不支持日期，时间或日期时间2并强制您转换为字符串文字 . 具体来说，Microsoft提到了这些数据类型的ODBC，OLE DB，JDBC和SqlClient问题，并且chart显示了每个数据类型的映射方式 .
如果值 compatability 超过精度，请使用datetime
旧问题......但是我想在这里添加一些尚未说明的东西...（注意：这是我自己的观察，所以不要求任何参考）
Datetime2 is faster when used in filter criteria.
TLDR:
在SQL 2016中，我有一个包含十万行的表和一个日期时间列ENTRY_TIME，因为它需要存储最多几秒的确切时间 . 在执行具有许多连接和子查询的复杂查询时，当我使用where子句时：
当有数百行时，查询很好，但当行数增加时，查询开始出现此错误：
我删除了where子句，并且意外地，查询在1秒内运行，尽管现在获取了所有日期的所有行 . 我用where子句运行内部查询，花了85秒，没有where子句花了0.01秒 .
我在这里遇到了很多关于这个问题的线程datetime filtering performance
我优化了一下查询 . 但我得到的真正速度是将datetime列更改为datetime2 .
现在，之前超时的同一查询只需不到一秒钟 .
干杯
我认为DATETIME2是存储日期的更好方法，因为它比DATETIME具有更高的效率 . 在SQL Server 2008中，您可以使用DATETIME2，它存储日期和时间，需要6-8个字节进行存储，精度为100纳秒 . 因此，任何需要更高时间精度的人都需要DATETIME2 .
上述SQL不适用于DateTime2字段 . 它返回并出错“操作数类型冲突：datetime2与int不兼容”
添加1以获得第二天是开发人员多年来一直在做的事情 . 现在微软有一个超级新的datetime2字段，无法处理这个简单的功能 .
“让我们使用比旧款更糟糕的新型号”，我不这么认为！