When working with SQL Server, it is almost inevitable that you will need to manipulate date and time values in your applications. The DATEADD function is a commonly used function that allows you to add or subtract a specified interval from a date or time value. However, there are some pitfalls that you need to be aware of when using this function.

Quiz – DATEADD Function

Let’s start with a quick quiz to test your knowledge of the DATEADD function. Take a moment to guess the output of the following queries:

SELECT DATEADD(MONTH, 1, '2015-01-28')
SELECT DATEADD(MONTH, 1, '2015-01-29')
SELECT DATEADD(MONTH, 1, '2015-01-30')
SELECT DATEADD(MONTH, 1, '2015-01-31')

Did you notice something strange in the output? The values are very similar, but not exactly what you might expect. The reason for this is that the DATEADD function adds or subtracts the specified interval to the date or time value. In this case, we are adding 1 month to the given dates. However, since the months have different numbers of days, the resulting dates are adjusted accordingly.

Now, let’s try another set of queries:

SELECT DATEADD(MONTH, 1, '2016-01-28')
SELECT DATEADD(MONTH, 1, '2016-01-29')
SELECT DATEADD(MONTH, -1, '2015-03-30')
SELECT DATEADD(MONTH, -1, '2015-03-31')

Again, the values might seem a bit unexpected. The important thing to note here is that the DATEADD function can use a positive or negative integer as the interval. In the first two queries, we are adding 1 month to the given dates. In the last two queries, we are subtracting 1 month. This is why the results are different.

Common Mistakes

Now, let’s talk about a common mistake that developers make when using the DATEADD function. Consider the following query:

SELECT DATEADD(YEAR, 8000, '2015-01-31');

If you execute this query, you will encounter an error: “Adding a value to a ‘datetime’ column caused an overflow.” The reason for this error is that the YEAR interval is not valid for the DATEADD function. The correct interval to use in this case should be “month” instead of “year”.

Conclusion

Understanding how to use date and time functions in SQL Server is crucial for any developer working with databases. The DATEADD function allows you to manipulate date and time values by adding or subtracting a specified interval. However, it is important to be aware of the potential pitfalls and to use the correct interval for the desired result.

Now that you have learned about the DATEADD function and its quirks, why not test your knowledge by answering the following questions:

1. Why do the results of the DATEADD function appear similar for different input dates?
2. Why does using the YEAR interval in the DATEADD function cause an overflow error?

Leave your answers in the comments section below before February 20th, 2015, for a chance to win a free subscription to Pluralsight.