Why can't I use LIKE '%datepart%' queries?

Several people want to offer the ability for users to enter any portion of the date (e.g. only the month, or only the year), and leave wildcards for the rest. So, for all rows in 2002, they would do something like this: 
 

SELECT columns FROM table WHERE dateColumn LIKE '%/2002 %'

 
Unfortunately, SQL Server does not internally store dates in the character format you see them represented as in client tools like Enterprise Manager or Query Analyzer. 
 
While for some odd reason Books Online recommends LIKE queries against datetime values, date range searches are much more effective because they don't rely on specific formatting of dates, regional settings on a server, etc. They will also have the ability to use an index, if it exists. Here is the above query in a much more efficient and logical format: 
 

SELECT columns FROM table WHERE      dateColumn >= '20020101'      AND dateColumn < '20030101'

 
Another interesting approach, albeit not as efficient as the above, is to use the shorthand datepart functions in SQL Server to match one or more criteria passed in. The following stored procedure will work for any combination of day, month and year: 
 

CREATE PROCEDURE dbo.ReturnRowsForAnyPartOfDate      @year INT = 0,      @month INT = 0,      @day INT = 0  AS  BEGIN      SELECT columns FROM table          WHERE          YEAR(dateColumn) = CASE              WHEN @year > 0 THEN                  @year              ELSE                  YEAR(dateColumn)              END          AND          MONTH(dateColumn) = CASE              WHEN @month > 0 THEN                  @month              ELSE                       MONTH(dateColumn)              END          AND          DAY(dateColumn) = CASE              WHEN @day > 0 THEN                  @day              ELSE                  DAY(dateColumn)              END  END

 
And you could call this from ASP as follows: 
 

<%      if request.form("y") <> "" then          y = clng(request.form("y"))          m = clng(request.form("m"))          d = clng(request.form("d"))      end if  %>    <form method=post name=dateSearch>        Year:            <select name=y>      <option value=0>Any      <%          for i = 2002 to 2000 step -1              response.write "<option value=" & i              if i = y then response.write " SELECTED"              response.write ">" & i          next      %>      </select>        Month:            <select name=m>      <option value=0>Any      <%          for i = 1 to 12              response.write "<option value=" & i              if i = m then response.write " SELECTED"              response.write ">" & monthname(i)          next      %>      </select>        Day:        <select name=d>      <option value=0>Any      <%          for i = 1 to 31              response.write "<option value=" & i              if i = d then response.write " SELECTED"              response.write ">" & i          next      %>      </select>        <input type=submit>    </form>    <hr size=1>    <%      if request.form("y") <> "" then          set Conn = CreateObject("ADODB.Connection")          Conn.open "<connection_string>"          sql = "EXEC dbo.ReturnRowsForAnyPartOfDate " & _              y & ", " & m & ", " & d          set rs = conn.execute(sql)          if not rs.eof then              do while not rs.eof                  response.write rs(0) & "<br>"                  rs.movenext              loop          else              response.write "No records."          end if          rs.close: set rs = nothing          conn.close: set conn = nothing      end if  %>