在SQL Server中删除datetime的时间部分的最佳方法 [英] Best approach to remove time part of datetime in SQL Server
问题描述
从SQL Server的datetime字段中删除时间部分时,哪种方法提供最佳性能?
a)选择DATEADD (dd,DATEDIFF(dd,0,getdate()),0)
或
b)select cast(convert(char(11),getdate(),113)as datetime)
第二种方法不会以任何方式发送几个字节,但可能不如转换速度那么重要。
两者也似乎都非常快,但是在处理数十万或更多行时,速度可能会有所不同?
另外,是否有可能有更好的方法来摆脱SQL中datetime的时间部分?
p>严格地说,方法 a
是资源密集度最低的:
a )选择DATEADD(dd,DATEDIFF(dd,0,getdate()),0)
CPU在对于同样的总持续时间一百万行,一些在他们手上的时间太多:在SQL Server中从日期+时间开始获取日期的最有效方法
我在其他地方看到类似的测试
我喜欢DATEADD / DATEDIFF,因为:
- varchar受到语言/日期格式问题的影响
示例:为什么我的CASE表达式不确定? - float依赖于内部存储
- 它通过更改0base $ b延伸到月的第一天,明天等$ b
编辑,2011年10月
对于SQL Server 2008+可以CAST到 date
。或者只需使用 date
,所以没有时间删除。
编辑,2012年1月 / p>
这是一个有效的例子:需要通过舍入时间或日期图在sql server中计算
编辑,2012年5月
不要在WHERE子句等中使用它,而不考虑:向列添加函数或CAST可以使索引使用失效。请参阅第2号: http:/ /www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/
现在,这样做有一个例子,管理CAST的SQL Server优化版本日期正确,但通常这将是一个坏主意...
Which method provides the best performance when removing the time portion from a datetime field in SQL Server?
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?
Strictly, method a
is the least resource intensive:
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?
I saw a similar test elsewhere with similar results too.
I prefer the DATEADD/DATEDIFF because:
- varchar is subject to language/dateformat issues
Example: Why is my CASE expression non-deterministic? - float relies on internal storage
- it extends to work out first day of month, tomorrow etc by changing "0" base
Edit, Oct 2011
For SQL Server 2008+, you can CAST to date
. Or just use date
so no time to remove.
Edit, Jan 2012
A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server
Edit, May 2012
Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here: 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 ...
这篇关于在SQL Server中删除datetime的时间部分的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!