SQL LIKE Statement on a DateTime Type

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

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

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

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%'

But this returns me nothing!

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)

Can anybody help?

I'm using SQL Server 2008.

Thanks in advance.

Answers


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

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

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.


If you need to use Like operator (for some reason) you have to convert the DateTime column into a varchar.

SELECT * 
FROM MyTable 
WHERE CONVERT(VARCHAR, CheckDate, 120) LIKE '%2009-1%'

You can use something like this:

SELECT * 
FROM MyTable 
WHERE CheckDate >= '2009-10-01' AND CheckDate < '2010-01-01';

Need Your Help

std::bind to std::function?

c++ function c++11 bind std

I get a compile error using this:

Full text search vs LIKE

sql-server full-text-search sql-like fulltext-index

My question is about using fulltext.As I know like queries which begin with % never use index :