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.
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';