关于DateTime类型的SQL LIKE语句 [英] SQL LIKE Statement on a DateTime Type

问题描述

如何在SQL Server中的 DateTime 数据类型的列上执行 LIKE 语句？

How do you perform a LIKE statement on a column of DateTime datatype in SQL Server?

如果我运行以下SQL，它将返回2009年所有日期。

If I run the following SQL it returns me all the dates with 2009 in.

SELECT * FROM MyTable where CheckDate LIKE '%2009%'

但是，如果我想要所有的10月，Nov&截止日期我希望能够执行以下操作：

However, if I want all the Oct, Nov & Dec dates I'd expect to be able to do the following:

SELECT * FROM MyTable where CheckDate LIKE '%2009-1%'

但这不给我什么！

我给用户一个过滤器选项，他们键入日期字符串，当他们键入I过滤数据。所以如果他们输入'20'，我想在日期之前返回所有的'20'的日期（所以这可能是所有的2012年的日期，或像06/20/1999这个日期值）

I'm giving the user a filter option where they type in the date string and as they type I filter the data. So if they type '20', I'd like to return all the dates with '20' within the date (so this could be all 2012 dates or a date value like 06/20/1999)

有人可以帮忙吗？

我使用SQL Server 2008。

I'm using SQL Server 2008.

提前致谢。

推荐答案

您可以使用 DATEPART 功能可以提取部分日期。它也应该使您的查询更清楚您正在寻求达到的目标：

You can use the DATEPART function to extract portions of dates. It should also make your queries more explicit about what you're seeking to achieve:

SELECT * FROM MyTable
where DATEPART(year,CheckDate)=2009 and
      DATEPART(month,CheckDate) between 10 and 12

（还有具体命名的功能，例如 MONTH 和 YEAR ，但我更喜欢 DATEPART ，因为它可以访问 datetime 的所有组件）

(There are also specifically named functions, such as MONTH and YEAR, but I prefer DATEPART for consistency since it can access all components of a datetime)

你应尽量避免将 datetime s视为具有任何类型的字符串格式。将它们视为字符串是我们遇到的最大错误来源之一。

You should try to avoid thinking of datetimes as having any kind of string format. Treating them as strings is one of the largest sources of errors we encounter.

这篇关于关于DateTime类型的SQL LIKE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！