T-SQL Tuesday #1: Date/Time Tricks

December 8, 2009 at 10:44 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , )


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.

4 Comments

  1. Tweets that mention T-SQL Tuesday #1: Date/Time Tricks « Home of the Scary DBA -- Topsy.com said,

    […] This post was mentioned on Twitter by Grant Fritchey and Grant Fritchey, Grant Fritchey. Grant Fritchey said: Posted my #tsqltuesday entry. Nothing special, just a caution: http://is.gd/5g2vg […]

  2. Mangal said,

    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)

  3. scarydba said,

    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.

  4. Vijaya Kadiyala said,

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

    Thanks — Vj
    http://www.DotNetVJ.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: