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)
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.
CREATE TABLE tab (col1 DATE, col2 DATETIME, col3 TIME);