Advanced Querying for DATETIME
The only thing I will discuss in this section, is whether you should prefer DATEADD() or DATEDIFF() when querying for DATETIME. Well, when you use comparison operators like >= or <=, I’m tempted to say, that there is no real big difference. Trying such queries against the Orders table almost always results in clustered index scans. However, I prefer the DATEADD() function. Why? Because you’re used to it’s syntax when you come across the following situation:
Suppose you want to find out, for internal logistics purposes, who placed an order 7 days from a given start date. This would look like:
DECLARE @dt DATETIME SET @dt = ‘19960701’ SELECT OrderID , CustomerID FROM Orders WHERE OrderDate = DATEADD(DAY,7,@dt)
OrderID CustomerID
———– ———-
10250 HANAR 10251 VICTE
(2 row(s) affected)
Since there is an index on OrderDate, your execution plan should look like this:
…|–Index Seek(OBJECT:([Northwind].[dbo].[Orders].[OrderDate])…
You see, SQL Server is using the index on OrderDate. What about the IO?
Table ‘Orders’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
Now we change the query to use DATEDIFF():
SELECT OrderID , CustomerID FROM Orders WHERE DATEDIFF(d,@dt,OrderDate)=7
OrderID CustomerID
———– ———-
10250 HANAR 10251 VICTE
(2 row(s) affected)
Of course, both rows are found. But what about the execution plan?
…|–Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders])…
Apparently, SQL Server cannot use the index on OrderDate because of the DATEDIFF function, and thus has no other choice but to scan the table. This is also reflected in the IO numbers:
Table ‘Orders’. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
More than 3 times more logical reads when using DATEDIFF!
Well, the Orders table does only contain 830 rows, so both queries execute pretty fast, but which query would you prefer on a large table? I know that I would certainly opt for the DATEADD() version.
How Do I Add (Subtract) N Days to the Current Date?
Well, the politically correct answer is to use the DATEADD() function, such as
DECLARE @dt DATETIME SET @dt = ‘20050325’ SELECT DATEADD(d,1,@dt)
——————————————————
2005-03-26 00:00:00.000
(1 row(s) affected)
However, since SQL Server’s base date unit is a day, you can do:
SELECT @dt+1
——————————————————
2005-03-26 00:00:00.000
(1 row(s) affected)
Both queries are equivalent and will add the specified amount of days to the input date. If you want to subtract from the input date, just give your specified amount of days a negative sign.
This will also work for fractions of a day. Suppose you want to add 2 hours to the above date. Using DATEADD this would look like:
DECLARE @dt DATETIME SET @dt = ‘20050325’ SELECT DATEADD(hh,2,@dt)
——————————————————
2005-03-25 02:00:00.000
(1 row(s) affected)
Likewise this also works:
SELECT @dt+0.08333333333333333
——————————————————
2005-03-25 02:00:00.000
(1 row(s) affected)
In the above example you see quite nicely why it pays to use DATEADD. It’s easier to understand, and you don’t need to recalculate the time fraction to a days’ basis, like I did. 2 hours are 2/24 day => 0.08333333333333333 day.