Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement
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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap