TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
 Specialized Dev Zones Research Center eBook Library .NET Java C++ Web Dev Architecture Database Security Open Source Enterprise Mobile Special Reports 10-Minute Solutions DevXtra Blogs Slideshow

# Best Practices for Date/Time Calculations in SQL Server : Page 2

## When can you use arithmetic operators in date/time calculations in SQL Server? When are the date/time functions provided by Microsoft the better option? Follow a few scenarios that demonstrate when arithmetic operators are safe and when they are risky.

 by Alex Kozak
 May 16, 2007
 Page 2 of 4
First Pitfalls
At this point, you may start to believe that using addition or subtraction operators in date/time calculations is completely safe. It isn't. For example try to calculate the interval between two dates (in days) using the first and second approaches as follows:
``````
DECLARE @dt1 datetime, @dt2 datetime;
SELECT @dt1 = 'Mar 17, 2007 09:09:00', @dt2 = 'Mar 17, 2007 22:09:00';
SELECT DATEDIFF(dd, @dt1, @dt2);
SELECT CAST((@dt2 - @dt1) as int);

Results:

0
1
``````

The results calculated by SQL Server's DATEDIFF() function and those using the subtraction operator are different. The first (0) is correct, the second (1) is wrong. Why? Well, when you convert a datetime value to the integer, the result will be rounded to the nearest integer. How it will be rounded depends on time portion of the datetime value. Consider the following example:

``````
DECLARE @dt3 datetime, @dt4 datetime;
SELECT @dt3 = 'Mar 17, 2007 11:59:59.994', @dt4 = 'Mar 17, 2007 11:59:59.997';
SELECT CAST(@dt3 AS int);
SELECT CAST(@dt4 AS int)
SELECT CAST(CAST(@dt3 AS int) AS datetime);
SELECT CAST(CAST(@dt4 AS int) AS datetime);

Results:

39156
39157
2007-03-17 00:00:00.000
2007-03-18 00:00:00.000
``````

As you can see, two time stamps with a 3ms interval were taken just before noon. If you convert the two date values to integers and then reconvert the results back to date values, you'll see a difference of one day. Similarly, two datetime values representing different days (Mar 16 and Mar 17) can be incorrectly converted to the same date, like this:

``````
DECLARE @dt3 datetime, @dt4 datetime;
SELECT @dt3 = 'Mar 16, 2007 12:00:01.000', @dt4 = 'Mar 17, 2007 11:59:59.994';
SELECT CAST(@dt3 AS int);
SELECT CAST(@dt4 AS int)
SELECT CAST(CAST(@dt3 AS int) AS datetime);
SELECT CAST(CAST(@dt4 AS int) AS datetime);

Results:

39156
39156
2007-03-17 00:00:00.000
2007-03-17 00:00:00.000
``````

Now, consider one more example. Suppose you have a table where you store sales transactions that each have an ID, time, and amount. You want to find the total amount and number of transactions per day, which is a very common task. Since your table is very large, you decide to speed up your query by grouping your transactions by date interval, not by date. But instead of using the SQL Server DATEDIFF() function, you decide to use an "advanced" approach: converting datetime data to integer or calculating the interval using simple arithmetic subtraction.

Here's what can happen in this scenario:

``````
SET NOCOUNT ON;
IF OBJECT_ID('sales', 'U') IS NOT NULL
DROP TABLE sales

CREATE TABLE sales(
transactionID int,
transactionTime datetime,
amount decimal(4,2));

INSERT INTO sales VALUES(1, 'Mar 17, 2007 08:00:23', 24.34);
INSERT INTO sales VALUES(2, 'Mar 17, 2007 10:33:23', 88.54);
INSERT INTO sales VALUES(3, 'Mar 17, 2007 12:00:44', 12.12);
INSERT INTO sales VALUES(4, 'Mar 17, 2007 14:23:23', 43.25);
INSERT INTO sales VALUES(5, 'Mar 17, 2007 16:45:22', 76.34);
INSERT INTO sales VALUES(6, 'Mar 17, 2007 17:11:22', 51.11);
INSERT INTO sales VALUES(7, 'Mar 17, 2007 19:45:23', 30.99);

SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount
FROM sales
GROUP BY DATEDIFF(dd, 0, transactionTime);

SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount
FROM sales
GROUP BY CAST(transactionTime AS int);

SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount
FROM sales
GROUP BY CAST((transactionTime - 0) AS int);

Results:

#Trans      totalAmount
----------- ---------------------------------------
7           326.69

#Trans      totalAmount
----------- ---------------------------------------
2           112.88
5           213.81

#Trans      totalAmount
----------- ---------------------------------------
2           112.88
5           213.81
``````

SQL Server DATEDIFF() produces the correct result because it works with dates like a FLOOR() function. It cuts the time portion of each date and manipulates only days. If you chose the other approach of converting to integers or direct subtraction, each datetime value would be rounded to the nearest integer, taking into account the time portion of the datetime value. This is why the second and third SELECT statements produce the wrong results.