Sql server® 2012 t-sql fundamentals

Filtering date ranges

When you need to filter a range of dates, such as a whole year or a whole month, it seems natural to 

use functions such as YEAR and MONTH. For example, the following query returns all orders placed in 

the year 2007.

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE YEAR(orderdate) = 2007;

However, you should be aware that in most cases, when you apply manipulation on the filtered 

column, SQL Server cannot use an index in an efficient manner. This is probably hard to understand 

without some background about indexes and performance, which are outside the scope of this book, 

but for now, just keep this general point in mind: To have the potential to use an index efficiently, you 

need to revise the predicate so that there is no manipulation on the filtered column, like this. 

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE orderdate >= '20070101' AND orderdate < '20080101';

Similarly, instead of using functions to filter orders placed in a particular month, like this:

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;

use a range filter, like the following.

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE orderdate >= '20070201' AND orderdate < '20070301';

www.it-ebooks.info

---

80  

Microsoft SQL Server 2012 T-SQL Fundamentals