Dec 08 2009

T-SQL Tuesday #1: Date/Time Tricks

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

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.


  • By Mangal, December 11, 2009 @ 3:28 am

    Well in the post of “how not to do date and time queries” you have used 1 trick that one should not use while quering datetime.

    You used BETWEEN on DATETIME column(well I’m not sure about the DDL of the table used and data type of TransactionDate). If it is DATETIME and not DATE column and time has captured then BETWEEN is not going to return all the rows from the date ‘2003/7/31′.
    So you have to be little careful with BETWEEN and querying DATETIME column.

    The best approch is
    >=min_date and < dateadd(day,1,max_date)

  • By scarydba, December 11, 2009 @ 5:59 am

    True. Like I said, you have to be very careful how you apply stuff you read on the internet. In this case, BETWEEN works fine, but I knew it was going to because I knew the data.

  • By Vijaya Kadiyala, December 15, 2009 @ 4:02 pm

    Its good tip like you mentioned people need to make sure that they dont use any functions on Indexed columns.

    Thanks — Vj

Other Links to this Post

  1. Tweets that mention T-SQL Tuesday #1: Date/Time Tricks « Home of the Scary DBA -- — December 8, 2009 @ 11:19 am

RSS feed for comments on this post. TrackBack URI

Leave a comment