RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

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


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.

Alex Kozak is a senior DBA/analyst working for SAP Canada. He has more than 15 years of database and programming experience. Microsoft has included some of his articles in the MSDN Library.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date