在SQL Server中移除日期时间的最佳方法是什么？

Question

在 SQL Server 中，从日期时间字段中删除时间部分的最佳方法是什么？

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

或者

b) select cast(convert(char(11), getdate(), 113) as datetime)

第二种方法在来回传输时会多发送一些字节，但与转换的速度相比可能不那么重要。

两种方法都似乎非常快，但处理数十万或更多行时速度可能有所不同？

此外，是否有可能存在更好的方法来消除SQL中日期时间的时间部分？

- Stephen Perelson

---

我已经在我的一个生产表中尝试了一百万条记录，但无论哪种方法都无法准确地读取性能。不过，两种方法返回的数据量完全相同。 - Stephen Perelson

在处理1800万行数据时（使用SQL Server 2008），我发现：方法b比方法a慢了大约24%。将“CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)”与方法a相比较，它慢了3.5%。综合性能考虑，方法a似乎是更好的选择。感谢大家提供的出色答案。 - Stephen Perelson

SQL为什么没有内置函数来完成这个操作？！！ - Gary McGill

SQL 2008的新日期数据类型可以处理这个。 - Philip Kelley

可能是重复的问题：在SQL Server中从日期+时间获取日期的最有效方法是什么？。 - Richard Szalay

Answer 1

严格来说，方法a是资源最少的：

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

据某个太有闲时间的人进行的测试，对于一百万行数据，以下方法相比其他方法更省CPU资源：Most efficient way in SQL Server to get a date from date+time?

我在别处也看到了类似测试结果。

我喜欢使用DATEADD/DATEDIFF的原因是：

• varchar 受语言和日期格式等问题影响
  例如：Why is my CASE expression non-deterministic?
• float 依赖于内部存储
• 可以通过改变“0”基准点来计算出每月第一天、明天等，而且具有扩展性

编辑，2011年10月

针对SQL Server 2008+，您可以将其CAST为date，例如：CAST(getdate() AS date)。或者直接使用date数据类型，这样就没有需要去除的time了。

编辑，2012年1月

以下是一个灵活运用该方法的示例：Need to calculate by rounded time or date figure in sql server

编辑，2012年5月

在WHERE条件等语句中不要轻率使用此方法，因为对列添加函数或CAST会导致索引失效。请参见这里的第2点：Common SQL Programming Mistakes

现在，这确实有一个关于后来的SQL Server优化器版本正确处理CAST到日期的示例，但通常情况下会是个坏主意……

编辑，2018年9月，适用于datetime2

DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'

select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)

- gbn

---

如果针对2011年10月的编辑，代码将是：选择转换(GETDATE()为日期)。 - Choco Smith

对于SQL的较新版本，使用日期（date）代替日期时间（datetime）可以避免处理小时数的需求。请使用以下示例：declare noTime date = getdate(), withTime datetime = getdate()，select @noTime,@withTime - ozkary

如果你只需要日期，那么将其转换为日期格式是很好的选择。然而，通常你需要在午夜时刻获取当前日期，以便进行一些进一步的日期操作。DATE数据类型在处理像日期加减、日期差异以及与其他日期/时间数据类型交互等方面非常受限制。对于这些情况，使用DATEADD()函数是最佳选择。 - Xedni

在2009年7月，当我回答时，“0218”是一个有效的日期，所以你不会走到这一步。另外，“0”不能转换为datetime2的19000101。请尝试此选择SELECT DATEDIFF(dd, '19000101', convert(datetime2(0), '0218-09-12', 120))。 - gbn

将变量命名为“epoch”是令人困惑的，因为它不是1970年，而是1900年。 1900年是“DATEDIFF”使用的最小值，因此继续使用1900年是有意义的。 变量应该重命名为其他名称。 - Rudey

Answer 2

在 SQL Server 2008 中，您可以使用：

CONVERT(DATE, getdate(), 101)

- Anto Raja Prakash

---

当从datetime转换为date时，第三个参数对结果没有任何影响，因此您的解决方案可以简化为CONVERT(DATE, getdate())，这已经被建议不止一次了。 - Andriy M

请使用CAST(GETDATE() AS DATE) 或者严格的 ANSI CAST(CURRENT_TIMESTAMP AS DATE)，我认为后者不必要。建议使用第一个。 - user2188550

Answer 3

当然，这是一个老帖子，但为了让它更加完整。

从SQL 2008版本开始，您可以使用日期数据类型，因此您只需要执行以下操作：

SELECT CONVERT(DATE,GETDATE())

- Arjan Fraaij

Answer 4

在SQL Server 2008中，有一个日期数据类型（也有时间数据类型）。

CAST(GetDate() as DATE)

或者

declare @Dt as DATE = GetDate()

- Metaphor

---

这是我使用的，效果很好。似乎是最简单的答案。与 CONVERT 结合使用有什么缺点吗？ - joelmdev

CAST和CONVERT在功能上是相等的。区别在于CAST属于ANSI标准，而CONVERT则是T-SQL特有的。因此，尽可能使用CAST。 - troy

@troy 我使用CAST是因为可以节省3个输入字母，而且语法比CONVERT更清晰，ANSI标准部分是无用的。 - user2188550

Answer 5

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

根据下方的评论，这并不是一个好的解决方案。

我可以删除这个回答，但我会把它留在这里作为反例，因为我认为评论者解释的为什么这不是一个好主意仍然很有用。

- Gary McGill

---

请参考GBN的答案，很多人已经对此进行了调查。DATETIME并不是以浮点数的形式存储的，因此使用DATEADD/DATEDIFF避免了在类型之间进行强制转换所需的数学操作。 - MatBailie

我可以接受你想避免从DATETIME到FLOAT的强制转换，但在那种情况下，OPs选项（a）中的零的隐式转换也是一个问题吗？嗯...我猜在那种情况下它不是一个FLOAT，并且服务器可能足够聪明以舍弃时间信息。好吧，我认输 :-) - Gary McGill

0 确实是从数字类型（我猜是 INT）隐式转换为 DATETIME。然而，因为它是一个常量表达式，优化器可以在编译存储过程时进行一次转换，并且仅需要为动态执行的 SQL 执行一次。简而言之，这只需要一次开销，而基于 FLOAT 的查询对每一行都有相当的开销。 - MatBailie

强制转换为浮点数非常不精确。 应该删除此答案。 没有人应该使用这段代码。 - usr

更不用说将时间转换为浮点数再转回日期时间是不安全的——浮点数精度不够。因此，我认为这种方法根本不能被推荐。更多详细信息请参见此帖子。 - ErikE

Answer 6

以下是来自另一个重复问题的答案：

SELECT CAST(CAST(getutcdate() - 0.50000004 AS int) AS datetime) 

这个神奇数字方法的性能略高于DATEADD方法。(看起来大约快了10%)

在处理数百万条记录的多轮测试中，CPU时间如下：

DATEADD   MAGIC FLOAT
500       453
453       360
375       375
406       360

但请注意，这些数字可能是无关紧要的，因为它们已经非常快了。除非我有超过100,000个记录集，否则CPU时间甚至无法读取以上零。

考虑到DateAdd是为此目的而设计的，并且更加强大，我建议使用DateAdd。

- Jeff Meatball Yang

---

这太糟糕了。我永远不会像这样冒险让我的数据处于风险之中。谁知道这是否适用于所有日期时间，而不仅仅是你测试过的那些。 - usr

@usr 噢，没问题，那只是一个神奇的数字，不应该用于那个目的。如果您想检查它的正确性，只需将一天内所有可能的日期都放在一个表中并检查结果！此外，有关更多信息，请参见此帖子。 - ErikE

@ErikE 很好的观点。你的回答提供了使用“'12:00:00.003'”的可能性，我认为这更好。 - usr

Answer 7

SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

- Byju

---

一个有效的选项，没错。但这个想法在此帖子中已经被提出过不止一次了。 - Andriy M

Answer 8

我非常喜欢：

[date] = CONVERT(VARCHAR(10), GETDATE(), 120)

使用120格式代码将强制日期符合ISO 8601标准：

'YYYY-MM-DD' or '2017-01-09'

在dplyr（R）和pandas（Python）中使用非常简单！

- emehex

Answer 9

注意！

方法a）和b）的输出结果并不总是相同的！

select DATEADD(dd, DATEDIFF(dd, 0, '2013-12-31 23:59:59.999'), 0)

输出：2014年01月01日00时00分00秒000毫秒

select cast(convert(char(11), '2013-12-31 23:59:59.999', 113) as datetime)

输出：2013年12月31日00:00:00.000

（在MS SQL Server 2005和2008 R2上进行了测试）

编辑：根据Adam的评论，如果您从表中读取日期值，则不会发生这种情况，但是如果您以文字形式提供日期值，则可能会发生（例如：作为通过ADO.NET调用的存储过程的参数）。

- broslav

---

在SQL Server的DATETIME列中无法存储.999。最高可用值为.997。从http://msdn.microsoft.com/en-us/library/ms187819.aspx可以看出，这些值会四舍五入到千分位为0、3或7。OP将无法在他们的表中看到您测试的值。 - Adam Wenger

你说得对。我本来不是想把这个作为回答发布给OP的问题，而是想让其他人看到它作为评论，但我只有11个声望点，而评论需要15个声望点。 - broslav

在你的第一个片段中，字符串常量被隐式转换为日期时间，在第二个片段中它仍然是一个字符串（113只是被忽略了）。 - Andriy M

Answer 10

CAST(round(cast(getdate()as real),0,1) AS datetime)

这种方法不使用字符串函数。 Date 基本上是一个实数数据类型，小数点前的数字表示一天的一部分。

我想这比很多方法都要快。

- shantanu singh chauhan

---

将类型转换为浮点数[不安全]（https://dev59.com/-XVD5IYBdhLWcg3wXaYd#3696991）。 - ErikE