Equivalence of correlated DATEADD intervals

Question

Is there any difference between the following statements?

SELECT DATEADD(MONTH, @SomeInteger * 12, @SomeDate)

SELECT DATEADD(YEAR, @SomeInteger, @SomeDate)

In other words, are there any cases in which they would yield different results for the same value of @SomeInteger and @SomeDate?

I ran a quick test via the following:

SELECT
    *
FROM dbo.DimDate dd
WHERE DATEADD(MONTH, 12, dd.Date) <> DATEADD(YEAR, 1, dd.Date)

which returned zero records, but that's far from proving the two statements are equivalent.

The computer scalar execution plan component has the exact same cost in both queries, but the ScalarOperator definitions are different - it would appear that the Query Optimizer is not converting one statement into another.

Thanks!

Answer 1

In that case they are equivalent behaviors even though the ScalarOperator definitions are different. This is because in other cases they can't be swapped. Think about:

• datediff in days, which depends on which month and also must account for leap year.
• datediff in seconds which will be non-deterministic when there is a leap second.
• etc.

SQL Server doesn't gain any advantage changing one scalar operator's definition to look more like another one's. What would be the benefit? If you said WHERE column = 4 * 5 wouldn't it be confusing if the scalar operator said WHERE column = 2 * 10? Even if you have another query somewhere that uses 2 * 10?

In other words, if you said DATEADD(MONTH, n*12, ... I see no reason at all for SQL Server to change that expression to DATEADD(YEAR, n, ... just because we all happen to know that there are 12 months in a year. You asked for a delta by months, not years, so that's what the expression should show.