CODEWITHSUNDEEP
sqlsql-serverdatetimeutc
Ali
Ali

Reputation: 3625

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)

I want to compare date without time

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

Upvotes: 49

Views: 136660

Answers (7)

rogers.wang
rogers.wang

Reputation: 456

declare @DateToday Date= '2019-10-1'; print @DateToday;

print Abs(datediff(day, @DateToday,CAST('oct 1 2019 7:00:00:000PM' AS DATETIME))) < 3

this is compare whin 3 days.

i test this on SQL Server 2014, it works.

Upvotes: -1

Marc Gravell
Marc Gravell

Reputation: 1063674

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

Upvotes: 36

SubhoM
SubhoM

Reputation: 811

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

Upvotes: 66

user3957458
user3957458

Reputation: 111

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.

Upvotes: 11

dknaack
dknaack

Reputation: 60516

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

Upvotes: 6

Oleg Dok
Oleg Dok

Reputation: 21766

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

Upvotes: -1

Ruzbeh Irani
Ruzbeh Irani

Reputation: 2438

select * from sampleTable 
where date_created ='20120215'

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

Upvotes: -3

Related Questions