SQL LIKE Statement on a DateTime Type

datetime, sql, sql-server-2008

Solution

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 `datetime`s as having any kind of string format. Treating them as strings is one of the largest sources of errors we encounter.

Problem

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.

Original source