最好的方法是在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)

I want to compare date without time

我想比较没有时间的日期

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

以上查询可以吗?或者你建议的其他更好的解决办法

• I am using SQL Server 2005
• 我正在使用SQL Server 2005
• Date saved in UTC format on server
• 在服务器上以UTC格式保存的日期
• Users against this data belongs different timezone
• 针对此数据的用户属于不同的时区

7 个解决方案

#1

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

不要使用转换——这毫无理由地涉及字符串。一个技巧是，datetime实际上是一个数字，天数是整数部分(时间是小数部分);因此，这一天是价值的底部:这只是数学，而不是字符串——快得多。

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

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

在您的例子中，不需要转换回datetime;使用范围允许最有效的比较(尤其是索引):

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

#2

Simple Cast to Date will resolve the problem.

到目前为止，简单的Cast可以解决这个问题。

DECLARE @Date datetime = '04/01/2016 12:01:31'

DECLARE @Date2 datetime = '04/01/2016'

SELECT CAST(@Date as date)

SELECT CASE When (CAST(@Date as date) = CAST(@Date2 as date)) Then 1 Else 0 End

#3

Description

Don't convert your Date to a varchar and compare because string comparisson is not fast.

不要将日期转换为varchar并进行比较，因为字符串比较不是很快的。

It is much faster if you use >= and < to filter your DateCreated column.

如果您使用>=和 <来过滤您的datecreated列，那么速度会快得多。< p>

If you have no parameter (like in your sample, a string) you should use the ISO Format <Year><Month><Day>.

如果您没有参数(比如在您的示例中，一个字符串)，您应该使用ISO格式 <月> 。

Sample

According to your sample

根据你的样品

DECLARE @startDate DateTime
DECLARE @endDate DateTime

SET @startDate = '20120215'
SET @endDate = DATEADD(d,1,@startDate)

SELECT * FROM sampleTable 
WHERE DateCreated >= @startDate AND DateCreated < @endDate

More Information

• MSDN - DATEADD (Transact-SQL)
• MSDN -返回(transact - sql)

#4

SELECT .......
FROM ........
WHERE 
CAST(@DATETIMEVALUE1 as DATE) = CAST(@DATETIMEVALUE2 as DATE)

The disadvantage is that you are casting the filter column.

缺点是你在铸造过滤柱。

If there is an index on the filter column, then, since you are casting, the SQL engine can no longer use indexes to filter the date more efficiently.

如果筛选器列上有一个索引，那么由于您正在进行强制转换，SQL引擎就不能再使用索引来更有效地筛选日期了。

#5

SELECT * from sampleTable  
WHERE 
datediff(day, DateCreated,CAST('Feb 15 2012  7:00:00:000PM' AS DATETIME))  = 0    

After performance testing the most obvious solutions, here is my result:

在性能测试最明显的解决方案之后，我的结果如下:

declare @mytime int, @othertime int, @othertime2 int
declare @i int = 0
declare @dummy int
declare @emptyloop int
declare @time datetime = getdate()

while @i < 100000
begin
set @i = @i + 1
end
set @emptyloop = datediff(microsecond, @time, getdate())

set @time = getdate()

set @i = 0
while @i < 100000
begin
select @dummy = 1 WHERE datediff(day, 0,getdate())  = 0
set @i = @i + 1
end
select @othertime = datediff(microsecond, @time, getdate()) - @emptyloop

set @time = getdate()

set @i = 0
while @i < 100000
begin
select @dummy = 1 WHERE datediff(day, 0,@i)  = 0
set @i = @i + 1
end
set @mytime = datediff(microsecond, @time, getdate()) - @emptyloop


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)  
set @time = getdate()
set @i = 0
while @i < 100000
begin
select @dummy = 1 WHERE @i >= @min and @i < @max
set @i = @i + 1
end
set @othertime2 = datediff(microsecond, @time, getdate()) - @emptyloop


select @mytime mytime, @othertime othertime, @othertime2 othertime2

Result:

结果:

mytime      othertime   othertime2
----------- ----------- -----------
117000      144000      147000

I tried to do it as exact as possible, sorry for the missing comments. Feel free to run the tests to check the overall results.

我试着尽可能准确地做这件事，很抱歉漏掉了评论。请随意运行测试来检查总体结果。

#6

Use 112 CONVERT's format

使用112转换的格式

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

or

或

if your sql server version 2008+ use DATE type

如果您的sql server版本2008+使用日期类型

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

#7

select * from sampleTable 
where date_created ='20120215'

This will also compare your column with the particular date without taking time into account

这也会将你的专栏与特定的日期进行比较，而不需要考虑时间。