Queries with DATEPART Equality, Range, and IN List Predicates

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.

For example, the DATE, TIME, or DTTM index is used in these queries:

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.

The appropriate DATEPART range and IN list predicate conditions for processing with DATE, TIME, and DTTM indexes are:

COMPARISON conditions >, <, >=, <=, !>, !<
One side of the operator is a date/time function or DATEPART function, with a parameter that is a table column or view column. The other side of the operator is a constant expression, such as an integer or integer type host variable. For example:
```
DATEPART(WEEK, col1) !<23
```
```
DATEPART(YEAR, col1) = 2001
```
```
HOUR(col3) >= 1
```
BETWEEN ... AND condition
The left side of BETWEEN is a date/time function or DATEPART function, with a parameter that is a table column or view column. Both sides of the AND are constant expressions, such as integers or integer type host variables. For example:
```
DATEPART(YEAR, col1) BETWEEN host-var1 AND host-var2
```
IN conditions
The left side of IN is a date/time function or DATEPART function, with a parameter that is a table column or view column. The values inside the IN list are constant expressions. For example:
```
DATEPART(MONTH, col1) IN (1999, 2001, 2003)
```

Note: The DATE, TIME, and DTTM indexes do not support some date parts (Calyearofweek, Calweekofyear, Caldayofweek, Dayofyear, Millisecond). For example:

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.