For a query with an equality predicate (= or !=), if one side of the comparison is a DATEPART expression or some other date and time function (for example, YEAR, QUARTER, DAY, MINUTE), and the other side of the comparison is a constant expression (including a constant value or host variable), then the DATE, TIME, or DTTM index is used (if the index is available) to get the result set.
SELECT * FROM tab WHERE DATEPART(YEAR, col1) = 2002; SELECT * FROM tab WHERE DATEPART(HOUR, col2) = 20; SELECT * FROM tab WHERE MINUTE (col3) != 30; SELECT * FROM tab WHERE DATEPART(MONTH, col2) = @tmon;where @tmon is an INTEGER host variable.
COMPARISON conditions >, <, >=, <=, !>, !<
DATEPART(WEEK, col1) !<23
DATEPART(YEAR, col1) = 2001
HOUR(col3) >= 1
BETWEEN ... AND condition
DATEPART(YEAR, col1) BETWEEN host-var1 AND host-var2
IN conditions
DATEPART(MONTH, col1) IN (1999, 2001, 2003)
SELECT * FROM tab WHERE DATEPART(MILLISECOND, col3) = 100; SELECT * FROM tab WHERE DATEPART(DAYOFYEAR, col1) <= 89;
In these cases, the query optimizer chooses other indexes to get the result.