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:

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

• Queries with range predicates (>, <, >=, <=, BETWEEN)

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:

• COMPARISON conditions >, <, >=, <=, !>, !<

  One side of the operator is a date/time function or DATEPART function, whose parameter 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, whose parameter 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, whose parameter 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)
  

SELECT * FROM tab WHERE DATEPART(MILLISECOND, col3)
= 100;

SELECT * FROM tab WHERE DATEPART(DAYOFYEAR, col1) <= 89;

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

• Compare condition:

  SELECT * FROM tab WHERE col1 < ‘2002/10/09’;
  
  SELECT * FROM tab WHERE col2 >= ‘2002/01/01 09:12:04.006’;
  

  One side of the comparison operator is a column name and the other side is a constant expression (constant value or host variable).

• Between condition:

  SELECT * FROM tab WHERE col3 BETWEEN ‘09:12:04.006’ AND ‘20:12:04.006’;
  
  SELECT * FROM tab WHERE col2 BETWEEN tmp_datetime1 AND tmp_datetime2;
  

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:

• The range of the predicate is exactly one or more years (the actual start date is the beginning of a year and the actual end date is the end of a year). For example,

  SELECT * FROM tab WHERE col1 BETWEEN ‘1993-01-01’ AND ‘1996-12-31’;
  
  SELECT * FROM tab WHERE col1 >= ‘1993-01-01’ AND
  col1 < ‘1997-01-01’;
  
  SELECT * FROM tab WHERE col2 BETWEEN ‘1993-01-01 00:00:00.000000’ AND ‘1996-12-31 23:59:59.999999’;
  

• The range of the predicate is exactly one or more months in the same year (the actual start date is the beginning of a month and the actual end date is the end of a month). For example,

  SELECT * FROM tab WHERE col1 > ‘1993-01-31’ AND
  col1 <= ‘1993-06-31’;
  
  SELECT * FROM tab WHERE col2 >= ‘1993-01-01 00:00:00.000000’ AND col1 < ‘1993-06-01 00:00:00.000000’;
  

• The range of the predicate is exactly one day. For example,

  SELECT * FROM tab WHERE col2 >= ‘1993-01-31 00:00:00.000000’ AND
  col2 <= ‘1993-01-31 23:59:59.999999’;
  

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’

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’

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.