SQL LIKE Statement on a DateTime Type

Question

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.

Answer 1

You can use something like this:

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