SQL Server中的DateTime2与DateTime

Which one:

哪一个:

• datetime
• datetime
• datetime2
• datetime2

is the recommended way to store date and time in SQL Server 2008+?

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

I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only?

我知道在精度(和存储空间)方面存在差异，但是暂时忽略这些差异，是否有关于何时使用什么的最佳实践文档，或者也许我们应该只使用datetime2 ?

14 个解决方案

#1

The MSDN documentation for datetime recommends using datetime2. Here is their recommendation:

datetime的MSDN文档建议使用datetime2。这是他们的推荐:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

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

datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage.

datetime2具有更大的日期范围、更大的默认分数精度和用户指定的可选精度。还取决于用户指定的精度，它可能使用更少的存储空间。

#2

DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999.

DATETIME2的日期范围为“0001 / 01 / 01”到“9999 / 12 / 31”，而DATETIME类型仅支持年份1753-9999。

Also, if you need to, DATETIME2 can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns.

而且，如果需要，DATETIME2可以在时间方面更加精确;DATETIME被限制为3 /3毫秒，而DATETIME2可以精确到100ns。

Both types map to System.DateTime in .NET - no difference there.

这两种类型都映射到系统。。net中的DateTime——没有区别。

If you have the choice, I would recommend using DATETIME2 whenever possible. I don't see any benefits using DATETIME (except for backward compatibility) - you'll have less trouble (with dates being out of range and hassle like that).

如果您有选择，我建议您尽可能使用DATETIME2。我不认为使用DATETIME有什么好处(除了向后兼容性)——您将会有更少的麻烦(日期超出范围并且像这样麻烦)。

Plus: if you only need the date (without time part), use DATE - it's just as good as DATETIME2 and saves you space, too! :-) Same goes for time only - use TIME. That's what these types are there for!

另外:如果你只需要日期(没有时间部分)，使用日期——它和DATETIME2一样好，也可以节省你的空间!:-)同样适用于时间-使用时间。这就是这些类型的用途!

#3

datetime2 wins in most aspects except (old apps Compatibility)

datetime2在大多数方面获胜，除了(旧的应用兼容性)

1. larger range of values
2. 更大范围的值
3. better Accuracy
4. 更好的精度
5. smaller storage space (if optional user-specified precision is specified)
6. 更小的存储空间(如果指定用户指定的可选精度)

please note the following points

请注意以下几点

• Syntax
  • datetime2[(fractional seconds precision=> Look Below Storage Size)]
  • datetime2[(分秒精度=>，低于存储大小)]
• 语法datetime2[(小数秒精度=>查看存储大小以下)]
• Precision, scale
  • 0 to 7 digits, with an accuracy of 100ns.
  • 0 ~ 7位，精度100ns。
  • The default precision is 7 digits.
  • 默认的精度是7位数。
• 精度，0 ~ 7位，精度100ns。默认的精度是7位数。
• Storage Size
  • 6 bytes for precision less than 3;
  • 6字节精度小于3;
  • 7 bytes for precision 3 and 4.
  • 7字节为精度3和4。
  • All other precision require 8 bytes.
  • 所有其他精度要求8字节。
• 存储大小为6字节，精度小于3;7字节为精度3和4。所有其他精度要求8字节。
• DateTime2(3) have the same number of digits as DateTime but uses 7 bytes of storage instead of 8 byte (SQLHINTS- DateTime Vs DateTime2)
• DateTime2(3)拥有与DateTime相同的数字，但是使用7字节的存储而不是8字节(sqlhint - DateTime Vs DateTime2)
• Find more on datetime2(Transact-SQL MSDN article)
• 更多关于datetime2的信息(Transact-SQL MSDN文章)

image source : MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - Implementation and Maintenance Chapter 3:Tables -> Lesson 1: Creating Tables -> page 66

图片来源:mct自学培训包(考试70 - 432):Microsoft®SQL Server®2008 -实现和维护第三章:表- >教训1:创建表- > 66页

#4

I concurr with @marc_s and @Adam_Poward -- DateTime2 is the preferred method moving forward. It has a wider range of dates, higher precision, and uses equal or less storage (depending on precision).

我同意@marc_s和@Adam_Poward的观点——DateTime2是向前推进的首选方法。它有更广泛的日期，更高的精度，并且使用相同或更少的存储(取决于精度)。

One thing the discussion missed, however...
@Marc_s states: Both types map to System.DateTime in .NET - no difference there. This is correct, however, the inverse is not true...and it matters when doing date range searches (e.g. "find me all records modified on 5/5/2010").

然而，有一件事是讨论漏掉了……@Marc_s状态:两种类型都映射到系统。在。net中的DateTime -没有区别。这是正确的，然而，逆是不正确的……在进行日期范围搜索时也很重要。“找到在5月5日修改的所有记录”)。

.NET's version of Datetime has similar range and precision to DateTime2. When mapping a .net Datetime down to the old SQL DateTime an implicit rounding occurs. The old SQL DateTime is accurate to 3 milliseconds. This means that 11:59:59.997 is as close as you can get to the end of the day. Anything higher is rounded up to the following day.

. net的Datetime版本与DateTime2具有相似的范围和精确度。当将.net Datetime映射到旧的SQL Datetime时，会出现隐式的四舍五入。旧的SQL DateTime精确到3毫秒。这意味着11:59:59.997是一天结束时你能得到的最接近的结果。任何高的都被收集到第二天。

Try this :

试试这个:

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'

Avoiding this implicit rounding is a significant reason to move to DateTime2. Implicit rounding of dates clearly causes confusion:

避免这种隐式舍入是迁移到DateTime2的一个重要原因。日期的隐式四舍五入明显导致混淆:

• Strange datetime behavior in SQL Server
• SQL Server中奇怪的datetime行为
• http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
• http://bytes.com/topic/sql - server/answers/578416奇怪毫秒- - datetime一部分数据- sql - server - 2000 a
• SQL Server 2008 and milliseconds
• SQL Server 2008和毫秒
• http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspx
• http://improve.dk/archive/2011/06/16/getting位- - datetime -圆-或- 235959 - 999 - ltgt.aspx的原因
• http://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx
• http://milesquaretech.com/blog/post/2011/09/12/datetime - vs datetime2 - sql - -圆-我- 999毫秒! . aspx

#5

DateTime2 wreaks havoc if you are an Access developer trying to write Now() to the field in question. Just did an Access -> SQL 2008 R2 migration and it put all the datetime fields in as DateTime2. Appending a record with Now() as the value bombed out. It was okay on 1/1/2012 2:53:04 PM, but not on 1/10/2012 2:53:04 PM.

如果您是一个试图将Now()写入相关字段的访问开发人员，则DateTime2会造成严重破坏。刚刚做了一个访问——> SQL 2008 R2迁移，它把所有的datetime字段都放到DateTime2中。当值被破坏时，附加一个带有Now()的记录。2012年1月1日下午2点53分04分还可以，但1月10日下午2点53分04分不行。

Once character made the difference. Hope it helps somebody.

一旦性格决定了区别。希望它能帮助别人。

#6

Here is an example that will show you the differences in storage size (bytes) and precision between smalldatetime, datetime, datetime2(0), and datetime2(7):

下面的示例将向您展示smalldatetime、datetime、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

which returns

它返回

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 if I want to store information down to the second - but not to the millisecond - I can save 2 bytes each if I use datetime2(0) instead of datetime or datetime2(7).

因此，如果我想将信息存储到秒(而不是毫秒)，那么如果我使用datetime2(0)而不是datetime或datetime2(7)，我可以每次保存2个字节。

#7

Almost all the Answers and Comments have been heavy on the Pros and light on the Cons. Here's a recap of all Pros and Cons so far plus some crucial Cons (in #2 below) I've only seen mentioned once or not at all.

几乎所有的答案和评论都侧重于正反两方面。以下是到目前为止所有正反两方面的总结，以及一些关键的正反两方面(在下面的第二条中)，我只看到过提到过一次或者根本没有提到过。

1. PROS:
2. 优点:

1.1. More ISO compliant (ISO 8601) (although I don’t know how this comes into play in practice).

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

1.2. More range (1/1/0001 to 12/31/9999 vs. 1/1/1753-12/31/9999) (although the extra range, all prior to year 1753, will likely not be used except for ex., in historical, astronomical, geologic, etc. apps).

1.2。更多的范围(1/1/0001到12/31/9999与1/1753-12/31/9999)(尽管在1753年之前的额外范围，除了在历史、天文、地质等应用程序中使用外，很可能不会使用)。

1.3. Exactly matches the range of .NET’s DateTime Type’s range (although both convert back and forth with no special coding if values are within the target type’s range and precision except for Con # 2.1 below else error / rounding will occur).

1.3。完全匹配。net的DateTime类型的范围(尽管如果值在目标类型的范围和精度之内，除了下面的Con # 2.1错误/舍入将会发生)。

1.4. More precision (100 nanosecond aka 0.000,000,1 sec. vs. 3.33 millisecond aka 0.003,33 sec.) (although the extra precision will likely not be used except for ex., in engineering / scientific apps).

1.4。更精确(100纳秒，即0.00000,1秒，比3.33毫秒，也就是0.003,33秒)(尽管在工程/科学应用中，额外的精度很可能不会被使用)。

1.5. When configured for similar (as in 1 millisec not "same" (as in 3.33 millisec) as Iman Abidi has claimed) precision as DateTime, uses less space (7 vs. 8 bytes), but then of course, you’d be losing the precision benefit which is likely one of the two (the other being range) most touted albeit likely unneeded benefits).

1.5。当配置为相似(如1毫秒的不是“相同”(如在3.33毫秒)伊曼阿宣称)精密DateTime,使用更少的空间(7和8字节),但是当然,你会失去精度的好处可能其中一个(另一个是范围)大多数吹捧虽然可能不必要的好处)。

2. CONS:
3. 缺点:

2.1. When passing a Parameter to a .NET SqlCommand, you must specify System.Data.SqlDbType.DateTime2 if you may be passing a value outside the SQL Server DateTime’s range and/or precision, because it defaults to System.Data.SqlDbType.DateTime.

2.1。当向. net命令传递参数时，必须指定System.Data.SqlDbType。DateTime2，如果您可能在SQL Server DateTime的范围和/或精度之外传递一个值，因为它默认为System.Data.SqlDbType.DateTime。

2.2. Cannot be implicitly / easily converted to a floating-point numeric (# of days since min date-time) value to do the following to / with it in SQL Server expressions using numeric values and operators:

2.2。不能隐式/容易地转换为浮点数值(从最小日期时间开始的天数)，以便使用数值和操作符在SQL Server表达式中对其执行以下操作:

2.2.1. add or subtract # of days or partial days. Note: Using DateAdd Function as a workaround is not trivial when you're needing to consider multiple if not all parts of the date-time.

2.2.1。增加或减少天数或部分天数。注意:如果需要考虑多个(如果不是所有的)日期时间，那么使用DateAdd函数作为解决方案并非易事。

2.2.2. take the difference between two date-times for purposes of “age” calculation. Note: You cannot simply use SQL Server’s DateDiff Function instead, because it does not compute age as most people would expect in that if the two date-times happens to cross a calendar / clock date-time boundary of the units specified if even for a tiny fraction of that unit, it’ll return the difference as 1 of that unit vs. 0. For example, the DateDiff in Day’s of two date-times only 1 millisecond apart will return 1 vs. 0 (days) if those date-times are on different calendar days (i.e. “1999-12-31 23:59:59.9999999” and “2000-01-01 00:00:00.0000000”). The same 1 millisecond difference date-times if moved so that they don’t cross a calendar day, will return a “DateDiff” in Day’s of 0 (days).

2.2.2。为“年龄”计算，取两个日期时间之间的差异。注意:你不能简单地使用SQL Server的DateDiff功能相反,因为它不计算年龄的大多数人所期望的,如果这两个相关发生交叉的日历/时钟日期-时间边界单元指定即使对于单位的一小部分,它会返回单位的区别1和0。例如，如果日期时间在不同的日历天(例如“1999-12-31 23:59:59.999999999”和“2000-01-01 00:00.0000000”)，那么日期日期间隔为2毫秒的日期编辑器将返回1 / 0(天)。相同的1毫秒差的日期-时间如果移动以使它们不跨越日历日，将返回一个“DateDiff”在天的0(天)。

2.2.3. take the Avg of date-times (in an Aggregate Query) by simply converting to “Float” first and then back again to DateTime.

2.2.3。获取日期-时间的Avg(在聚合查询中)，只需先转换为“Float”，然后再转换为DateTime。

NOTE: To convert DateTime2 to a numeric, you have to do something like the following formula which still assumes your values are not less than the year 1970 (which means you’re losing all of the extra range plus another 217 years. Note: You may not be able to simply adjust the formula to allow for extra range because you may run into numeric overflow issues.

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

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 – Source: “ 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 -来源:“https://siderite.blogspot.com/2015/08/howto - translation -sql-datetime2-to.html”

Of course, you could also Cast to DateTime first (and if necessary back again to DateTime2), but you'd lose the precision and range (all prior to year 1753) benefits of DateTime2 vs. DateTime which are prolly the 2 biggest and also at the same time prolly the 2 least likely needed which begs the question why use it when you lose the implicit / easy conversions to floating-point numeric (# of days) for addition / subtraction / "age" (vs. DateDiff) / Avg calcs benefit which is a big one in my experience.

当然，您也可以先转换到DateTime(如果需要，还可以转换回DateTime2)，但你会失去精度和范围(前1753年)的好处DateTime2和DateTime也我想2最大,同时我想2最不可能需要这引出了一个问题为什么使用它,当你失去的隐式/容易转换浮点数字的天)(#加/减/“年龄”(与DateDiff)/ Avg calc是大在我的经验中获益。

Btw, the Avg of date-times is (or at least should be) an important use case. a) Besides use in getting average duration when date-times (since a common base date-time) are used to represent duration (a common practice), b) it’s also useful to get a dashboard-type statistic on what the average date-time is in the date-time column of a range / group of Rows. c) A standard (or at least should be standard) ad-hoc Query to monitor / troubleshoot values in a Column that may not be valid ever / any longer and / or may need to be deprecated is to list for each value the occurrence count and (if available) the Min, Avg and Max date-time stamps associated with that value.

顺便说一句，日期-时间的Avg是(或者至少应该是)一个重要的用例。a)除了用于获取日-时间(因为使用了一个公共的基准日期-时间)来表示持续时间(一个常见的实践)时的平均持续时间(b)之外，对于获取一个仪表板类型的统计信息(关于一个范围/一组行的日期-时间列中的平均日期-时间)也很有用。c)标准(或至少应该是标准)特别查询监控/排除值列,可能不再是有效的/和/或可能需要弃用是为每个值发生数和列表(如果可用)分钟,日期-时间相关的平均和最大的价值。

#8

Interpretation of date strings into datetime and datetime2 can be different too, when using non-US DATEFORMAT settings. E.g.

在使用非us datetime设置时，将日期字符串解释为datetime和datetime2也可以有所不同。如。

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

This returns 2013-05-06 (i.e. May 6) for datetime, and 2013-06-05 (i.e. June 5) for datetime2. However, with dateformat set to mdy, both @d and @d2 return 2013-06-05.

这将返回2013-05-06(即5月6日)的datetime，以及2013-06-05(即6月5日)的datetime2。然而，将dateformat设为mdy， @d和@d2都返回2013-06-05。

The datetime behavior seems at odds with the MSDN documentation of SET DATEFORMAT which states: Some character strings formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting. Obviously not true!

datetime行为似乎与SET DATEFORMAT的MSDN文档不一致，该文档声明:一些字符串格式(例如ISO 8601)是独立于DATEFORMAT设置的。显然不是真的!

Until I was bitten by this, I'd always thought that yyyy-mm-dd dates would just be handled right, regardless of the language / locale settings.

在我被这句话深深吸引之前，我一直认为yyyyyy -mm-dd日期处理得很好，无论语言/语言环境设置如何。

#9

while there is increased precision with datetime2, some clients doesn't support date, time, or datetime2 and force you to convert to a string literal. Specifically Microsoft mentions "down level" ODBC, OLE DB, JDBC, and SqlClient issues with these data types and has a chart showing how each can map the type.

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

If value compatability over precision, use datetime

如果值可压缩性超过精度，则使用datetime

#10

According to this article, if you would like to have the same precision of DateTime using DateTime2 you simply have to use DateTime2(3). This should give you the same precision, take up one fewer bytes, and provide an expanded range.

根据本文，如果您希望使用DateTime2获得与datetime相同的精度，只需使用DateTime2(3)。这将给您同样的精度，占用一个更少的字节，并提供一个扩展的范围。

#11

I just stumbled across one more advantage for DATETIME2: it avoids a bug in the Python adodbapi module, which blows up if a standard library datetime value is passed which has non-zero microseconds for a DATETIME column but works fine if the column is defined as DATETIME2.

我刚刚偶然发现了DATETIME2的另一个优势:它避免了Python adodbapi模块中的一个错误，如果传递了一个标准库datetime值(对于DATETIME2列有非零的微秒)，则该模块会崩溃，但是如果将该列定义为DATETIME2，那么它会正常工作。

#12

Select ValidUntil + 1
from Documents

The above SQL won't work with a DateTime2 field. It returns and error "Operand type *: datetime2 is incompatible with int"

上面的SQL不能使用DateTime2字段。它返回并出错“操作数类型冲突:datetime2与int不兼容”

Adding 1 to get the next day is something developers have been doing with dates for years. Now Microsoft have a super new datetime2 field that cannot handle this simple functionality.

增加1以获得第二天是开发人员多年来一直在做的事情。现在，微软有一个超级新datetime2字段，不能处理这个简单的功能。

"Let's use this new type that is worse than the old one", I don't think so!

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

#13

Old Question... But I want to add something not already stated by anyone here... (Note: This is my own observation, so don't ask for any reference)

老问题…但是我想补充一些这里没有人说过的东西……(注:这是我自己的看法，所以不要要求任何参考)

Datetime2 is faster when used in filter criteria.

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

TLDR:

TLDR:

In SQL 2016 I had a table with hundred thousand rows and a datetime column ENTRY_TIME because it was required to store the exact time up to seconds. While executing a complex query with many joins and a sub query, when I used where clause as:

在SQL 2016中，我有一个包含十万行和一个datetime列ENTRY_TIME的表，因为它需要将确切的时间存储到秒。当执行包含多个连接和子查询的复杂查询时，当我使用where子句作为:

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

The query was fine initially when there were hundreds of rows, but when number of rows increased, the query started to give this error:

查询最初在有数百行时是正常的，但当行数增加时，查询开始出现以下错误:

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

I removed the where clause, and unexpectedly, the query was run in 1 sec, although now ALL rows for all dates were fetched. I run the inner query with where clause, and it took 85 seconds, and without where clause it took 0.01 secs.

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

I came across many threads here for this issue as datetime filtering performance

在这个问题上，我遇到了很多线程，比如datetime过滤性能

I optimized query a bit. But the real speed I got was by changing the datetime column to datetime2.

我对查询进行了一些优化。但是我得到的实际速度是将datetime列更改为datetime2。

Now the same query that timed out previously takes less than a second.

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

cheers

干杯

#14

I think DATETIME2 is the better way to store the date, because it has more efficiency than the DATETIME. In SQL Server 2008 you can use DATETIME2, it stores a date and time, takes 6-8 bytes to store and has a precision of 100 nanoseconds. So anyone who needs greater time precision will want DATETIME2.

我认为DATETIME2是更好的存储日期的方式，因为它比DATETIME具有更高的效率。在SQL Server 2008中，您可以使用DATETIME2，它存储日期和时间，需要6-8字节来存储，精度为100纳秒。因此，任何需要更大时间精度的人都需要DATETIME2。