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 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 16, 2007
 Page 4 of 4

### WEBINAR:On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

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.
Comment and Contribute

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

Thanks for your registration, follow us on our social networks to keep up-to-date