Best way to compare dates without time in SQL Serv

问题:

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
• Date saved in UTC format on server
• Users against this data belongs different timezone

回答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

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):

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.

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.

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

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

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)

回答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.

回答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

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

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