sql - 在SQL Server中删除日期时间的时间部分的最佳方法(Best approach to remove time part of datetime in SQL Server)

Question

Which method provides the best performance when removing the time portion from a datetime field in SQL Server?

(从SQL Server的datetime字段中删除时间部分时，哪种方法提供最佳性能？)

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

or

(要么)

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

The second method does send a few more bytes either way but that might not be as important as the speed of the conversion.

(第二种方法确实会发送更多的字节，但这可能不如转换速度那么重要。)

Both also appear to be very fast, but there might be a difference in speed when dealing with hundreds-of-thousands or more rows?

(两者看起来都非常快，但是处理成千上万行或更多行时速度可能有所不同？)

Also, is it possible that there are even better methods to get rid of the time portion of a datetime in SQL?

(另外，是否有更好的方法可以消除SQL中日期时间的时间部分？)

  ask by Stephen Perelson translate from so

Answer 1

Strictly, method a is the least resource intensive:

(严格来说，方法a是最少的资源消耗：)

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

Proven less CPU intensive for same total duration a million rows by some one with way too much time on their hands: Most efficient way in SQL Server to get date from date+time?

(事实证明，在同一总持续时间（一百万行）中，有人占用太多时间，CPU占用较少的时间： SQL Server中从date + time获取日期的最有效方法？)

I saw a similar test elsewhere with similar results too.

(我在其他地方也看到了类似的测试，结果也差不多。)

I prefer the DATEADD/DATEDIFF because:

(我更喜欢DATEADD / DATEDIFF，因为：)

• varchar is subject to language/dateformat issues

  (varchar受语言/日期格式问题的约束)
  Example: Why is my CASE expression non-deterministic?

  (示例： 为什么我的CASE表达式是不确定的？)

• float relies on internal storage

  (浮动依赖于内部存储)

• it extends to work out first day of month, tomorrow etc by changing "0" base

  (它通过更改“ 0”为基础扩展到每月的第一天，明天等)

Edit, Oct 2011

(编辑，2011年10月)

For SQL Server 2008+, you can CAST to date .

(对于SQL Server 2008+，您可以CAST date 。)

Or just use date so no time to remove.

(或者只是使用date所以没有时间删除。)

Edit, Jan 2012

(编辑，2012年1月)

A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server

(一个如此灵活的工作示例： 需要在SQL Server中按四舍五入的时间或日期来计算)

Edit, May 2012

(编辑，2012年5月)

Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage.

(不要在WHERE子句等中使用它，而无需考虑：向列中添加函数或CAST会使索引使用无效。)

See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

(请在此处查看编号2： http ： //www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/)

Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but generally it will be a bad idea ...

(现在，这确实有一个示例，说明可以正确管理CAST的最新SQL Server优化程序版本，但通常这是一个坏主意...)

Edit, Sep 2018, for datetime2

(编辑日期为2018年9月)

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)