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:

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.

Mangalsaid,

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.