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

Solution 1:

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)=2009and
      DATEPART(month,CheckDate) between10and12

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

Solution 2:

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

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

Solution 3:

You can use something like this:

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