Recommended Use of DATE and DTTM Index Types

Use a DATE or DTTM index when the DATE, DATETIME, or TIMESTAMP column is used in queries containing date and time functions and operations.

Queries with DATEPART equality predicates (=, !=), DATEPART range predicates (>, <, >=, <=, !>, !<, BETWEEN) and DATEPART IN list predicates
Queries with range predicates (>, <, >=, <=, BETWEEN)

Note: For a simple equality predicate (no DATEPART) with a DATE, DATETIME, or TIMESTAMP column, LF and HG indexes provide the best performance. If an LF or HG index is unavailable, the DATE or DTTM index is used to get the result.

If a DATE, DATETIME, or TIMESTAMP column is used in the GROUP BY clause or in the WHERE/HAVING clauses for equalities (including join conditions) or IN predicates, the column needs an LF or HG index, as only these indexes can perform fast equality.

The table tab used in the examples contains columns defined as follows:

CREATE TABLE tab
(col1 DATE,
 col2 DATETIME,
 col3 TIME);

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.
Queries with Range Predicates
Examine how your queries use range predicates when indexing predicate columns.

Parent topic: Date (DATE) and Datetime (DTTM) Indexes