Best way to compare dates without time in SQL Server

Question

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

possible duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) — gbn, Feb 16 '12 at 09:40
Possible duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) — abatishchev, Mar 21 '17 at 17:56

score 62 · Answer 1 · edited Apr 11 '16 at 16:15

62

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

edited Apr 11 '16 at 16:15

Haroldo Gondim

7,725
9
43
62

answered Apr 11 '16 at 15:13

SubhoM

771
5
6

score 36 · Accepted Answer · edited Aug 07 '18 at 22:16

36

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

edited Aug 07 '18 at 22:16

Harsh Baid

7,199
5
48
92

answered Feb 16 '12 at 09:39

Marc Gravell

1,026,079
266
2,566
2,900

1

Been tested, and keeping in the datetime domain is best http://stackoverflow.com/a/1177529/27535 – gbn Feb 16 '12 at 09:48
@gbn Interesting. It is pretty close, though, between the two. The key take-away point from that: don't use strings ;p – Marc Gravell Feb 16 '12 at 09:51
1

I also prefer staying within datetime (rather than casting to float) as I prefer to treat datetime's numeric representation as an implementation detail (i.e., that days is the integer part of a `datetime`). – Brian Feb 16 '12 at 15:26

score 11 · Answer 3 · edited Aug 19 '14 at 18:16

11

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.

edited Aug 19 '14 at 18:16

Fabian Fagerholm

4,099
1
35
45

answered Aug 19 '14 at 17:52

user3957458

111
1
2

dknaack · Answer 4 · 2012-02-16T14:13:31.353

6

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)

edited Feb 16 '12 at 14:13

answered Feb 16 '12 at 09:36

dknaack

60,192
27
155
202

1

minor: re the `where` construction - it would need testing, but I would **expect** an obvious range-based approach (`>= @min and < @max`) to work better, especially for indexed data. – Marc Gravell Feb 16 '12 at 09:53
@MarcGravell I think it makes sense if he want data between two dates but he wants only data from one day. not ? – dknaack Feb 16 '12 at 09:55
the range can be exactly one day, trivially; but it should allow direct use of the index, rather than having to do any computations per row. – Marc Gravell Feb 16 '12 at 09:57
SQL Server chose the same plan for both - IO stats are the same – Marc Gravell Feb 16 '12 at 10:01
Yes my result is the same. The Optimizer is awesome ;) – dknaack Feb 16 '12 at 10:02
1

ah, no: reads are the same - but CPU for the second is much higher: http://pastie.org/3393454 - meaning: the range approach is more efficient – Marc Gravell Feb 16 '12 at 10:18
In my plan i can't see a significant difference. – dknaack Feb 16 '12 at 10:27
1

For those of you who likes performance, my solution is faster according to my test – t-clausen.dk Feb 16 '12 at 10:29
While I agree with your first statement (don't convert to varchar), I disagree that applying dateadd/datediff directly to the column is the best approach. Not just for efficiency (I understand the optimizer might make sense of this, but clearly the CPU still has to work harder), but also because you have to change your methodology if you need to get data for more than a day. Open-ended range is much more flexible and allows you to be consistent. Some other thoughts here : https://sqlblog.org/dates – Aaron Bertrand Feb 16 '12 at 13:58
@AaronBertrand Thats right, the CPU has more work. My answer is updated. Thanks! – dknaack Feb 16 '12 at 14:14

score -1 · Answer 5 · answered Jul 29 '19 at 08:52

-1

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.

answered Jul 29 '19 at 08:52

rogers.wang

416
5
11

score -1 · Answer 6 · answered Feb 16 '12 at 09:36

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

score -3 · Answer 7 · answered Feb 16 '12 at 09:36

-3

select * from sampleTable 
where date_created ='20120215'

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

answered Feb 16 '12 at 09:36

Ruzbeh Irani

2,318
18
10

2

casting to varchars and then comparing is way slower than comparing dates – t-clausen.dk Feb 16 '12 at 09:37
2

This will not work. The OP want all rows for the given date, not only the `2012-02-15 00:00.000` data. – dknaack Feb 16 '12 at 09:41

Best way to compare dates without time in SQL Server

7 Answers7

Description

Sample

More Information

Linked