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.

by Alex Kozak

May 15, 2007

Page 4 of 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:

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.