Using the "magic" values of
1900 (the year of the "zero" date) and
0 (or "date zero" which is
'1900-01-01') we can produce the 1st January of the two years we need (5 years ago and 1 year ahead):
WHERE DATEADD(year, 2016 - 1900 - 5, 0)
AND date < DATEADD(year, 2016 - 1900 + 1, 0) ;
In recent versions (2012+), it's easier with the
DATEFROMPARTS(year, month, day) function:
WHERE DATEFROMPARTS(2016 - 5, 1, 1)
AND date < DATEFROMPARTS(2016 + 1, 1, 1) ;
Note: the query - because the condition is against the column (
date) and not on an expression - may* use an index on that column. Queries that have conditions with expressions on the columns (like
YEAR(date) >= ..) are unlikely to use an index, unless there is an index on a computed column with the specific expression or a similar indexed view.
* "may": Since we want data that spans 6 years - so probably a large portion of the table - a table scan may still be used. Using similar conditions ("SARGable") is considered good practice though. Consider the case where the clustered index of the table is or starts with the
date column. Immediately the plan can be a partial scan instead of a table scan. The
YEAR(date) queries will still have to do a full table scan.