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:

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.



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.

| improve this answer | |
  • Hi Aaron, thanks for the answer! Do you have any source/code that indicates that DATEADD(MONTH, @n*12... is always identical to DATEADD(YEAR, @n...? – Cowthulhu Apr 16 '19 at 18:54
  • @Cowthulhu No, but what would be the point of proving that? I also don't have any source/code that proves that dateadd(minute,60) is identical to dateadd(hour,1), even if I don't know off-hand of any edge cases where they would differ. – Aaron Bertrand Apr 16 '19 at 18:55
  • I guess that's sort of the point of the question - apologies if it was ambiguous! Lines 1-3 of my question were what I was aiming to "resolve". Edited my question to clear it up - added the fourth line. – Cowthulhu Apr 16 '19 at 18:56
  • I understand the question you're asking, I just don't understand the point, and I find it very hard to prove that no counter-example exists. It's like proving there are no bears in the forest just because you haven't seen any yet today. – Aaron Bertrand Apr 16 '19 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.