blocks|key|1207490|text|您可以使用DATEPART()函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1207491|SELECT+*+FROM+record+
WHERE++(DATEPART(yy,+register_date)+=+2009
AND++++DATEPART(mm,+register_date)+=+10
AND++++DATEPART(dd,+register_date)+=+10)|code-block|syntax|javascript|1207492|我发现这种方法很容易读懂，因为它忽略了时间部分，而且您不必使用第二天的日期来限制您的选择。您可以通过添加额外的子句，使用适当的DatePart代码来达到更高或更小的粒度，例如|1207493|AND++++DATEPART(hh,+register_date)+=+12)|1207494|以获取在12和1之间创建的记录。|1207495|有关有效参数的完整列表，请参阅MSDN+DATEPART+docs。|offset|length|1207496|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-au/library/ms174420.aspx^0|0|0|0|0|0|F|I|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@$O|14|P|15|1|16]]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

You could use the DATEPART() function

<pre><code>SELECT * FROM record 
WHERE (DATEPART(yy, register_date) = 2009
AND DATEPART(mm, register_date) = 10
AND DATEPART(dd, register_date) = 10)
</code></pre>

I find this way easy to read, as it ignores the time component, and you don't have to use the next day's date to restrict your selection. You can go to greater or lesser granularity by adding extra clauses, using the appropriate DatePart code, e.g.

<pre><code>AND DATEPART(hh, register_date) = 12)
</code></pre>

to get records made between 12 and 1.

Consult the <a href="http://msdn.microsoft.com/en-au/library/ms174420.aspx" rel="noreferrer">MSDN DATEPART docs</a> for the full list of valid arguments.

blocks|key|1422397|text|对DATETIME变量没有LIKE运算符的直接支持，但您始终可以将DATETIME转换为VARCHAR：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422398|SELECT+(list+of+fields)+FROM+YourTable
WHERE+CONVERT(VARCHAR(25),+register_date,+126)+LIKE+'2009-10-10%25'|code-block|syntax|javascript|1422399|在MSDN+docs中查看CONVERT函数中可用“样式”的完整列表。|offset|length|1422400|Marc|1422401|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/aa226054%2528SQL.80%2529.aspx^0|0|0|1|9|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

There's no direct support for LIKE operator against DATETIME variables, but you can always cast the DATETIME to a VARCHAR:

<pre><code>SELECT (list of fields) FROM YourTable
WHERE CONVERT(VARCHAR(25), register_date, 126) LIKE '2009-10-10%'
</code></pre>

Check the <a href="http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx" rel="noreferrer">MSDN docs</a> for a complete list of available "styles" in the CONVERT function.

Marc

blocks|key|1422460|text|如果这样做，就会强制它进行字符串转换。最好是构建一个开始/结束日期范围，并使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422461|declare+@start+datetime,+@end+datetime
select+@start+=+'2009-10-10',+@end+=+'2009-11-10'
select+*+from+record+where+register_date+>=+@start
+++++++++++and+register_date+<+@end|code-block|syntax|javascript|1422462|这将允许它使用索引(如果register_date上有索引)，而不是表扫描。|offset|length|style|CODE|1422463|entityMap^0|0|0|C|D|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you do that, you are forcing it to do a string conversion. It would be better to build a start/end date range, and use:

<pre><code>declare @start datetime, @end datetime
select @start = '2009-10-10', @end = '2009-11-10'
select * from record where register_date &gt;= @start
 and register_date &lt; @end
</code></pre>

This will allow it to use the index (if there is one on <code>register_date</code>), rather than a table scan.

blocks|key|1422496|text|您可以使用CONVERT来获取文本格式的日期。如果将其转换为varchar(10)，则可以使用=而不是like：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422497|select+*
from+record
where+CONVERT(VARCHAR(10),register_date,120)+=+'2009-10-10'|code-block|syntax|javascript|1422498|或者，您可以使用上限和下限日期，附加的优点是它可以利用索引：|1422499|select+*
from+record
where+'2009-10-10'+<=+register_date
and+register_date+<+'2009-10-11'|1422500|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can use CONVERT to get the date in text form. If you convert it to a varchar(10), you can use = instead of like:

<pre><code>select *
from record
where CONVERT(VARCHAR(10),register_date,120) = '2009-10-10'
</code></pre>

Or you can use an upper and lower boundary date, with the added advantage that it could make use of an index:

<pre><code>select *
from record
where '2009-10-10' &lt;= register_date
and register_date &lt; '2009-10-11'
</code></pre>

blocks|key|1422687|text|不幸的是，无法使用'LIKE‘将datetime与varchar进行比较，但可以通过另一种方式获得所需的输出。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422688|++++select+*+from+record+where+datediff(dd,[record].[register_date],'2009-10-10')=0|code-block|syntax|javascript|1422689|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Unfortunately, It is not possible to compare datetime towards varchar using 'LIKE'
But the desired output is possible in another way.

<pre><code> select * from record where datediff(dd,[record].[register_date],'2009-10-10')=0
</code></pre>

blocks|key|1207542|text|您还可以使用convert来使日期可使用LIKE进行搜索。例如,|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1207543|select+convert(VARCHAR(40),create_date,121)+,+*+from+sys.objects+where+++++convert(VARCHAR(40),create_date,121)+LIKE+'%2517:34%25'|code-block|syntax|javascript|1207544|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can also use convert to make the date searchable using LIKE. For example, 

<pre><code>select convert(VARCHAR(40),create_date,121) , * from sys.objects where convert(VARCHAR(40),create_date,121) LIKE '%17:34%'
</code></pre>

blocks|key|1207640|text|LIKE运算符不能处理日期部分，但DATEPART运算符可以。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1207641|命令查找开户日期为1号的所有账户：|1207642|SELECT+*+
++FROM+Account+
+WHERE+DATEPART(DAY,+CAST(OpenDt+AS+DATE))+=+1`|code-block|syntax|javascript|1207643|*强制转换OpenDt，因为它的值在DATETIME中，而不仅仅是DATE。|1207644|entityMap^0|0|4|H|8|0|0|0|5|6|I|8|X|4|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$]]

The <code>LIKE</code> operator does not work with date parts like month or date but the <code>DATEPART</code> operator does.

Command to find out all accounts whose Open Date was on the 1st:

<pre><code>SELECT * 
 FROM Account 
 WHERE DATEPART(DAY, CAST(OpenDt AS DATE)) = 1`
</code></pre>

*CASTING <code>OpenDt</code> because it's value is in <code>DATETIME</code> and not just <code>DATE</code>.

blocks|key|1422760|text|我这样解决了我的问题。感谢您的改进建议。C#中的示例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422761|string+dd,+mm,+aa,+trc,+data;
dd+=+nData.Text.Substring(0,+2);
mm+=+nData.Text.Substring(3,+2);
aa+=+nData.Text.Substring(6,+4);
trc+=+"-";
data+=+aa+%2B+trc+%2B+mm+%2B+trc+%2B+dd;

"Select+*+From+bdPedidos+Where+Data+Like+'%25"+%2B+data+%2B+"%25'";|code-block|syntax|javascript|1422762|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I solved my problem that way. Thank you for suggestions for improvements.
Example in C#.

<pre><code>string dd, mm, aa, trc, data;
dd = nData.Text.Substring(0, 2);
mm = nData.Text.Substring(3, 2);
aa = nData.Text.Substring(6, 4);
trc = "-";
data = aa + trc + mm + trc + dd;

"Select * From bdPedidos Where Data Like '%" + data + "%'";
</code></pre>

blocks|key|1422833|text|我意识到这是一个古老的问题，但这里的许多答案在这里都没有给出SARGable答案，也没有涵盖参数分解。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1422834|首先，使用>=和<逻辑要好得多。对于您想要的日期，则如下所示：|offset|length|style|CODE|1422835|SELECT+{Your+Columns}
FROM+dbo.record
WHERE+register_date+>=+'20091010'
++AND+register_date+<+'20091011';|code-block|syntax|javascript|1422836|这将包括2009-10-10的每个时间值，包括当天午夜的钟声，以及2009-10-11之前的一纳秒。|1422837|但是，通常情况下，您需要对查询进行参数设置，因此您可以使用DATEADD在第二个子句中添加一个日期：|1422838|DECLARE+@DateParam+date+=+'20091010';

SELECT+{Your+Columns}
FROM+dbo.record
WHERE+register_date+>=+@DateParam
++AND+register_date+<+DATEADD(DAY,1,@DateParam);|1422839|这将维护SARGability，并意味着可以使用register_date上的任何索引。|1422840|entityMap^0|0|5|2|8|1|0|0|0|T|7|0|0|O|D|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@$D|15|E|16|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|J|7|17|8|@]|9|@]|A|$K|L]]|$1|S|3|T|5|6|7|18|8|@$D|19|E|1A|F|G]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|V|$]]

I realise this an old question, but a lot of the answers here don't give a SARGable answer here, nor cover parmetrisation.
First off, you are far better off using <code>&gt;=</code> and <code>&lt;</code> logic. For the date you want, then that would look like this:
<pre class="lang-sql prettyprint-override"><code>SELECT {Your Columns}
FROM dbo.record
WHERE register_date &gt;= '20091010'
 AND register_date &lt; '20091011';
</code></pre>
This'll include every time value on 2009-10-10, including the stroke of midnight on the day, and a nanosecond prior to 2009-10-11.
Often, however, you'll be parametrising your query, so instead what you can do is use <code>DATEADD</code> to add a day to the second clause:
<pre class="lang-sql prettyprint-override"><code>DECLARE @DateParam date = '20091010';

SELECT {Your Columns}
FROM dbo.record
WHERE register_date &gt;= @DateParam
 AND register_date &lt; DATEADD(DAY,1,@DateParam);
</code></pre>
This maintains SARGability and means that any indexes on <code>register_date</code> can be used.

in MySQL 

<pre><code>select * from record where register_date like '2009-10-10%'
</code></pre>

What is the syntax in SQL Server?

SQL Server datetime LIKE select?

在MySQL中select * from record where register_date like '2009-10-10%'SQL Server中的语法是什么？

问SQL Server datetime如select？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server datetime如select？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server datetime如select？
EN