Recommended Use of DATE, TIME, and DTTM Index Types

Use a DATE, TIME, or DTTM index in the following cases, when the DATE, TIME, 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, TIME, DATETIME, or TIMESTAMP column, LF and HG indexes have the best performance. If an LF or HG index is not available, then the DATE, TIME, or DTTM index is used to get the result.

If a DATE, TIME, 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 do fast equality.

The table tab used in the examples in this section 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: The Date (DATE), Time (TIME), and Datetime (DTTM) Index Types

Related concepts

Additional Indexes