Recommended use

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:

NoteFor 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. See also the section “Additional indexes” for index recommendations for DATE, TIME, DATETIME, and TIMESTAMP columns.

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.

For example, the DATE, TIME, or DTTM index is used in the following 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:

NoteThe 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.


Queries with range predicates

In the following cases with range predicates, a DATE, TIME, or DTTM index is chosen to process the queries:

For these types of queries, a DATE, TIME, or DTTM index is usually faster than a HNG index.

In three specific cases, use of the DATE or DTTM index may significantly improve performance:

NoteIn the three cases above, you must be careful about the concepts of range of years, range of months, and exactly one day. For example, there are four cases for a DTTM index that are recognized as range of years:

col2 >  ’year1/12/31 23:59:59.999999’ and
col2 <  ’year2/01/01 00:00:00.000000’

col2 >= ’year1/01/01 00:00:00.000000’ and
col2 <  ’year2/01/01 00:00:00.000000’

col2 >  ’year1/12/31 23:59:59.999999’ and
col2 <= ’year2/12/31 23:59:59.999999’

col2 >= ’year1/01/01 00:00:00.000000’ and
col2 <= ’year2/12/31 23:59:59.999999’

Ranges as in the following examples do not match range of years:

col2 > ’year1/12/31 23:59:59.999999’ and
col2 <= ’year2/01/01 00:00:00.000000’

col2 > ’year1/01/01 00:00:00.000000’ and
col2 <  ’year2/01/01 00:00:00.000000’

The first range does not match, because it includes the value 'year2/01/01 00:00:00:000000' in addition to the range of years. The second range loses the value 'year1/01/01 00:00:00.000000.'

Similar specifics apply to range of months, and exactly one day, for both DTTM and DATE indexes.

If a small date range (less than 60 values) does not fit the three specific cases above, then LF and HG indexes are faster than the DATE index.