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

Bonus Benefits Beyond Bad Results
SQL Server's date/time functions not only help you avoid the errors, but they also make the programmer's life much easier and more convenient. If, for example, you need to generate the fifth day of each month in the year 2007, you can do that easily using the DATEADD() function:

SELECT DATEADD(mm, num - 1, 'Jan 05, 2007') AS [5th_Day]
	FROM sequence
	WHERE num <= 12

Results:

5th_Day
-----------------------
2007-01-05 00:00:00.000
2007-02-05 00:00:00.000
2007-03-05 00:00:00.000
2007-04-05 00:00:00.000
2007-05-05 00:00:00.000
2007-06-05 00:00:00.000
2007-07-05 00:00:00.000
2007-08-05 00:00:00.000
2007-09-05 00:00:00.000
2007-10-05 00:00:00.000
2007-11-05 00:00:00.000
2007-12-05 00:00:00.000

The beauty of this query is in its convenience. You do not need to know how many days are in each specific month or in the year. The logic and calculations are already included into the SQL Server functions and are transparent to you. Trying to use the arithmetic operator (+) for the same task would require implementing that logic yourself, which would increase the development and debugging time and potentially raise the chance of errors.

So should you use arithmetic operators in date/time calculations in SQL Server? No. Although it's possible to use arithmetic operators with dates for very simple operations such as GETDATE() + 1—because SQL Server will convert the 1 to a date internally—you're better off avoiding using arithmetic operators with dates altogether. If you use them in more complex date/time calculations, you will need to be very careful and conscientious.

Previous Page