blocks|key|2528132|text|描述|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2528133|不要将日期转换为varchar并进行比较，因为字符串比较速度不快。|2528134|如果使用>=和<来过滤DateCreated列，速度会快得多。|offset|length|style|CODE|2528135|如果没有参数(如示例中的字符串)，则应使用ISO格式的<Year><Month><Day>。|2528136|示例|2528137|根据您的示例执行|2528138|DECLARE+@startDate+DateTime
DECLARE+@endDate+DateTime

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

SELECT+*+FROM+sampleTable+
WHERE+DateCreated+>=+@startDate+AND+DateCreated+<+@endDate|code-block|syntax|javascript|2528139|更多信息|BOLD|2528140|2528141|MSDN+-+DATEADD+(Transact-SQL)|unordered-list-item|2528142|2528143|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms186819.aspx^0|0|0|4|2|7|1|B|B|0|R|I|0|0|0|0|0|4|0|0|0|T|0|T|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1C|8|@$F|1D|G|1E|H|I]|$F|1F|G|1G|H|I]|$F|1H|G|1I|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|1J|8|@$F|1K|G|1L|H|I]]|9|@]|A|$]]|$1|L|3|M|5|6|7|1M|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1N|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1O|8|@]|9|@]|A|$S|T]]|$1|U|3|V|5|6|7|1P|8|@$F|1Q|G|1R|H|W]]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|10|7|1T|8|@$F|1U|G|1V|H|W]]|9|@$F|1W|G|1X|1|1Y]]|A|$]]|$1|11|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|20|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]]]

<h3>Description</h3>

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

It is much faster if you use <code>&gt;=</code> and <code>&lt;</code> to filter your <code>DateCreated</code> column. 

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

<h3>Sample</h3>

According to your sample

<pre><code>DECLARE @startDate DateTime
DECLARE @endDate DateTime

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

SELECT * FROM sampleTable 
WHERE DateCreated &gt;= @startDate AND DateCreated &lt; @endDate
</code></pre>

<h3>More Information</h3>

<ul>
<li><a href="http://msdn.microsoft.com/en-us/library/ms186819.aspx" rel="nofollow">MSDN - DATEADD (Transact-SQL)</a></li>
</ul>

blocks|key|48230|text|select+*+from+sampleTable+
where+date_created+='20120215'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|48231|这还会将您的列与特定日期进行比较，而不考虑时间|unstyled|48232|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select * from sampleTable 
where date_created ='20120215'
</code></pre>

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

blocks|key|48234|text|使用112转换格式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|48235|select+*+
from+sampleTable+
where+
++CONVERT(VARCHAR(20),DateCreated,112)+
=+++++CONVERT(VARCHAR(20),CAST('Feb+15+2012++7:00:00:000PM'+AS+DATETIME),112)|code-block|syntax|javascript|48236|或|offset|length|style|BOLD|48237|如果您sql+server版本2008%2B使用DATE类型|CODE|48238|select+*+from+sampleTable+
where+CONVERT(DATE,DateCreated)+
=+++++CONVERT(DATE,CAST('Feb+15+2012++7:00:00:000PM'+AS+DATETIME))|48239|entityMap^0|0|0|0|1|0|M|4|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|V|8|@$I|W|J|X|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Y|8|@$I|Z|J|10|K|O]]|9|@]|A|$]]|$1|P|3|Q|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|R|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|S|$]]

Use 112 CONVERT's format 

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

or 

if your sql server version 2008+ use <code>DATE</code> type

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

blocks|key|48250|text|不要使用convert+-它会无缘无故地涉及到字符串。一个诀窍是，日期时间实际上是一个数字，而天是整数部分(时间是小数部分)；因此，天是该值的FLOOR：这只是数学问题，而不是字符串-要快得多|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|48251|declare+@when+datetime+=+GETUTCDATE()
select+@when+--+date+%2B+time
declare+@day+datetime+=+CAST(FLOOR(CAST(@when+as+float))+as+datetime)
select+@day+--+date+only|code-block|syntax|javascript|48252|在您的例子中，不需要转换回日期时间；使用范围可以实现最有效的比较(特别是在索引的情况下)：|48253|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|48254|entityMap^0|1Z|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

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

<pre><code>declare @when datetime = GETUTCDATE()
select @when -- date + time
declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime)
select @day -- date only
</code></pre>

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

<pre><code>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 &gt;= @min and DateCreated &lt; @max
</code></pre>

blocks|key|48602|text|SELECT+.......
FROM+........
WHERE+
CAST(@DATETIMEVALUE1+as+DATE)+=+CAST(@DATETIMEVALUE2+as+DATE)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|48603|缺点是您正在强制转换筛选器列。|unstyled|48604|如果筛选列上有索引，则由于是强制转换，因此SQL引擎不能再使用索引来更有效地筛选日期。|48605|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT .......
FROM ........
WHERE 
CAST(@DATETIMEVALUE1 as DATE) = CAST(@DATETIMEVALUE2 as DATE)
</code></pre>

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.

blocks|key|47889|text|简单地转换为Date就可以解决这个问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|47890|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|code-block|syntax|javascript|47891|entityMap^0|6|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Simple Cast to <code>Date</code> will resolve the problem. 

<pre><code>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
</code></pre>

blocks|key|2528224|text|声明@DateToday+Date=+'2019-10-1'；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2528225|print+Abs(datediff(day，@DateToday，CAST('oct+1+2019+7:00:00:000+AS‘AS+DATETIME)<3|2528226|这是3天内的比较。|2528227|我在SQL+Server+2014上测试了它，它工作正常。|2528228|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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

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

this is compare whin 3 days.

i test this on SQL Server 2014, it works.

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

I want to compare date without time 

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

<ul>
<li>I am using SQL Server 2005</li>
<li>Date saved in UTC format on server</li>
<li>Users against this data belongs different timezone</li>
</ul>

Best way to compare dates without time in SQL Server

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)我想比较不带时间的日期上面的...

问在SQL Server中比较不带时间的日期的最佳方法
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server中比较不带时间的日期的最佳方法EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server中比较不带时间的日期的最佳方法
EN