blocks|key|194760|text|下面是一个示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|194761|我有一个Order表，其中有一个名为OrderDate的DateTime字段。我要检索订单日期等于2006年1月1日的所有订单。接下来有几种方法可以做到：|194762|1)+WHERE+DateDiff(dd,+OrderDate,+'01/01/2006')+=+0
2)+WHERE+Convert(varchar(20),+OrderDate,+101)+=+'01/01/2006'
3)+WHERE+Year(OrderDate)+=+2006+AND+Month(OrderDate)+=+1+and+Day(OrderDate)=1
4)+WHERE+OrderDate+LIKE+'01/01/2006%25'
5)+WHERE+OrderDate+>=+'01/01/2006'++AND+OrderDate+<+'01/02/2006'|code-block|syntax|javascript|194763|已找到here|offset|length|194764|entityMap|0|LINK|mutability|MUTABLE|url|http://www.demiliani.com/blog/archive/2006/01/19/3384.aspx^0|0|0|0|3|4|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Here is an example:

I've an Order table with a DateTime field called OrderDate. I want to retrieve all orders where the order date is equals to 01/01/2006. there are next ways to do it:

<pre><code>1) WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
2) WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
3) WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
4) WHERE OrderDate LIKE '01/01/2006%'
5) WHERE OrderDate &gt;= '01/01/2006' AND OrderDate &lt; '01/02/2006'
</code></pre>

Is found <a href="http://www.demiliani.com/blog/archive/2006/01/19/3384.aspx">here</a>

blocks|key|3033266|text|您可以添加一个只包含日期而不包含时间的计算列。在这两个选项中，我会选择BETWEEN操作符，因为它对我来说更“干净”，而且应该更好地利用索引。比较执行计划似乎表明BETWEEN会更快；然而，在实际测试中，它们执行的是相同的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3033267|entityMap^0|Z|7|29|7|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

You could add a calculated column that includes only the date without the time. Between the two options, I'd go with the <code>BETWEEN</code> operator because it's 'cleaner' to me and should make better use of indexes. Comparing execution plans would seem to indicate that <code>BETWEEN</code> would be faster; however, in actual testing they performed the same.

blocks|key|2820801|text|在任何情况下，转换为DATE或使用无限制的日期范围都将产生最佳性能。仅供参考，使用索引转换为日期是性能最好的。更多测试不同技术的文章:+Aaron+Bertrand发布的What+is+the+most+efficient+way+to+trim+time+from+datetime?|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2820802|摘自那篇文章：|2820803|DECLARE+@dateVar+datetime+=+'19700204';

--+Quickest+when+there+is+an+index+on+t.[DateColumn],+
--+because+CONVERT+can+still+use+the+index.
SELECT+t.[DateColumn]
FROM+MyTable+t
WHERE+=+CONVERT(DATE,+t.[DateColumn])+=+CONVERT(DATE,+@dateVar);

--+Quicker+when+there+is+no+index+on+t.[DateColumn]
DECLARE+@dateEnd+datetime+=+DATEADD(DAY,+1,+@dateVar);
SELECT+t.[DateColumn]+
FROM+MyTable+t
WHERE+t.[DateColumn]+>=+@dateVar+AND+
++++++t.[DateColumn]+<+@dateEnd;|code-block|syntax|javascript|2820804|同样来自那篇文章:使用BETWEEN、DATEDIFF或CONVERT(CHAR(8)...都比较慢。|2820805|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime^0|A|4|2D|1M|0|0|0|0|B|7|J|8|S|I|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|6|7|12|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|13|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

Converting to a <code>DATE</code> or using an open-ended date range in any case will yield the best performance. FYI, convert to date using an index are the best performers. More testing a different techniques in article: <a href="http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime">What is the most efficient way to trim time from datetime?</a> Posted by Aaron Bertrand

From that article:

<pre><code>DECLARE @dateVar datetime = '19700204';

-- Quickest when there is an index on t.[DateColumn], 
-- because CONVERT can still use the index.
SELECT t.[DateColumn]
FROM MyTable t
WHERE = CONVERT(DATE, t.[DateColumn]) = CONVERT(DATE, @dateVar);

-- Quicker when there is no index on t.[DateColumn]
DECLARE @dateEnd datetime = DATEADD(DAY, 1, @dateVar);
SELECT t.[DateColumn] 
FROM MyTable t
WHERE t.[DateColumn] &gt;= @dateVar AND 
 t.[DateColumn] &lt; @dateEnd;
</code></pre>

Also from that article: using <code>BETWEEN</code>, <code>DATEDIFF</code> or <code>CONVERT(CHAR(8)...</code> are all slower.

blocks|key|195255|text|当日期在fromdate和toDate之间时获取项。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|195256|where+convert(date，fromdate，103)+<=+'2016-07-26‘和convert(date，toDate，103+)+>=+'2016-07-26’|195257|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Get items when the date is between fromdate and toDate.

where 
convert(date, fromdate, 103 ) &lt;= '2016-07-26' and convert(date, toDate, 103) >= '2016-07-26'

I have a SQL <code>datetime</code> field in a very large table. It's indexed and needs to be queried.

The problem is that SQL always stores the time component (even though it's always midnight), but the searches are to the day, rather than time.

<pre><code>declare @dateVar datetime = '2013-03-11;

select t.[DateColumn]
from MyTable t
where t.[DateColumn] = dateVar;
</code></pre>

Won't return anything, as the <code>t.[DateColumn]</code> always includes a time component.

My question is what is the best way round this?

There seem to be two main groups of options:

<ol>
<li>Create a second variable using <code>dateadd</code> and use a <code>between ... and</code> or <code>&gt;= ... and ... &lt;=</code>. </li>
<li>Convert the <code>t.[DateColumn]</code> into a date-only component - I think this will cause any indexes to be ignored.</li>
</ol>

Both of these seem very messy - I don't really want to be making a range comparison or scan the table.

Is there a better way?

If one of these options is consistently optimal way then how and why?

What is the optimal way to compare dates in Microsoft SQL server?

我在一个非常大的表中有一个SQL datetime字段。它被编入了索引，需要查询。问题是SQL总是存储时间部分(即使一直是午夜)，但是搜索是当天的，而不是时间。declare @dateVar datetime = '2013-03-11;select t.[DateColumn]from MyTable twhere...

问在Microsoft SQL server中比较日期的最佳方法是什么？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Microsoft SQL server中比较日期的最佳方法是什么？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Microsoft SQL server中比较日期的最佳方法是什么？
EN