在SQL Server中比较无时间的最佳方式 [英] Best way to compare dates without time in SQL Server

问题描述

select * from sampleTable 
where CONVERT(VARCHAR(20),DateCreated,101) 
=     CONVERT(VARCHAR(20),CAST('Feb 15 2012  7:00:00:000PM' AS DATETIME),101)

以上查询是否正常？或其他更好的解决方案您建议

Is above query is ok? or other better solution you suggest

• 我使用SQL Server 2005


• 以UTC格式保存的日期在服务器上


• 针对此数据的用户属于不同的时区

推荐答案

不要使用转换 - 没有任何理由涉及字符串。一个诀窍是datetime实际上是一个数字，而days是整数部分（时间是小数部分）;因此这一天是价值的 FLOOR ：这只是数学，而不是字符串 - 快得多

Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster

declare @when datetime = GETUTCDATE()
select @when // date + time
declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime)
select @day // date only

在你的情况下，不需要转换回datetime;并且使用范围允许最有效的比较（特别是如果索引）：

In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed):

declare @when datetime = 'Feb 15 2012  7:00:00:000PM'
declare @min datetime = FLOOR(CAST(@when as float))
declare @max datetime = DATEADD(day, 1, @min)

select * from sampleTable where DateCreated >= @min and DateCreated < @max

这篇关于在SQL Server中比较无时间的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！