Have you ever encountered a situation where the SQL Server DATEADD function didn’t work as expected? In this article, we will discuss a bug related to the DATEADD function in SQL Server and explore a workaround to overcome it.

Let’s start by understanding the purpose of the DATEADD function. The DATEADD function in SQL Server allows you to add or subtract a specified time interval (such as years, months, days, etc.) to a given date. It is commonly used to perform date calculations and manipulations in SQL queries.

However, there is a bug in SQL Server when the DATEADD function is combined with the SYSDATETIME() function. In such cases, SQL Server incorrectly estimates the expression and ignores the DATEADD function. This bug does not occur when using the GETDATE() function.

Let’s take a look at an example to understand this bug:

SELECT
  Orders.Id ,
  Orders.DateAndTime ,
  OrderStatusId
FROM
  Sales.Orders AS Orders
WHERE
  Orders.DateAndTime > DATEADD (YEAR , -2 , SYSDATETIME())

In the above query, we are trying to retrieve orders that have a DateAndTime value greater than two years ago from the current date and time. However, due to the bug, SQL Server estimates the expression to be SYSDATETIME() and ignores the DATEADD function.

To overcome this bug and get accurate results, we can use a workaround. The workaround involves calculating the value of the DATEADD function separately and then executing the query using dynamic execution. Here’s an example:

DECLARE
    @ThresholdDate DATETIME2 = DATEADD (YEAR , -2 , SYSDATETIME())
EXECUTE sp_executesql N'
    SELECT
      Orders.Id ,
      Orders.DateAndTime ,
      OrderStatusId
    FROM
      Sales.Orders AS Orders
    WHERE
      Orders.DateAndTime > @ThresholdDate', N'@ThresholdDate DATETIME2', @ThresholdDate

By calculating the value of the DATEADD function before executing the query dynamically, we can ensure that SQL Server estimates the expression correctly and retrieves the desired results.

If you have encountered this bug in your SQL Server environment, I encourage you to vote for its resolution on the Microsoft Connect platform. Your vote can help Microsoft prioritize and fix this bug quickly.

In conclusion, the SQL Server DATEADD function is a powerful tool for performing date calculations and manipulations. However, it is important to be aware of the bug related to the combination of DATEADD and SYSDATETIME(). By using the workaround mentioned in this article, you can overcome this bug and ensure accurate results in your SQL queries.

Thank you for reading!