I’m going to try out Adam Machanic’s idea for a blog party. The topic this month are Date/Time tricks.
Instead of supplying a trick for Date/Time, I’m going to caution you about the tricks that you use. Let’s take a simple issue. You want to pull back data from a table, let’s use the Production.TransactionHistoryArchive in AdventureWorks2008, for a given month of data. Before we run the query, let’s create an index on the table:
CREATE INDEX ixTest ON Production.TransactionHistoryArchive (TransactionDate)
The query itself is pretty simple. This is one mechanism that will retrieve the data for the month of July in 2003:
SELECT tha.TransactionID FROM Production.TransactionHistoryArchive AS tha WHERE DATEPART(yy,tha.TransactionDate) = 2003 AND DATEPART(mm,tha.TransactionDate) = 7
In theory you should be able to use the index that was created earlier, but instead, you’ll see this execution plan:
The problem is occuring because there is a function running against the columns. This is going to force a scan, even though you have a good index. Rewriting the query so that it looks like this:
SELECT tha.ProductionID FROM Production.TransactionHistoryArchive AS tha WHERE tha.TransactionDate BETWEEN '2003/7/1' AND '2003/7/31'
Eliminates the function on the column so that the execution plan is now a nice clean index seek:
Whatever tricks you begin to apply to date/time, be careful of how you apply them. And, if you try a fix like I supplied above, be sure it returns the data you think it returns, testing is the key to applying anything you read on the internet.