what is the type of interval in SQL Server DATEADD() Function? [closed]

Question

For example:

SELECT DATEADD(month, 2, '2017/08/25') AS DateAdd;

I think it would be more sense to define argument as a specificed data type such as chars, just like the 'U' argument in OBJECT_ID function

IF OBJECT_ID('dbo.Digits', 'U') IS NOT NULL 
    DROP TABLE dbo.Digits;

So isn't it more technically correct to define argument from month to 'month':

SELECT DATEADD('month', 2, '2017/08/25') AS DateAdd;

and is month a data type in SQL Server?

Can I think it as an analogy to macro in C like:

#define month "month"

So what's month in SQL Server?

Answer 1

MONTH is not a data type (in this context or in any other context), it's a defined argument (think of it like a constant). The list is here. Why it isn't enclosed in a string? You'd have to find the people who wrote the code 30 years ago and ask them. The document even says (with [my clarification added]):

DATEADD does not accept user-defined variable equivalents [e.g. strings] for the datepart arguments.

The DATEDIFF_BIG documentation states it better, IMHO:

DATEDIFF_BIG will not accept datepart values from user-defined variables or as quoted strings.

This is likely due to how the parser was written back then and lack of reason to change it 30 years later because someone doesn't like it. :-) In addition to the above two functions, you'd also have to change DATENAME, DATEDIFF, DATEPART, etc. to be consistent. At this stage in SQL Server's lifecycle, I just don't see the benefit.

And the DATE* functions aren't alone. Another example like this is OPENQUERY which you could argue should be able to take variables for either argument, or a string representing the server for the first argument (to be more consistent with OPENDATASOURCE and OPENROWSET). But the documentation states:

OPENQUERY does not accept variables for its arguments.

---

Next, you asked:

So isn't it more technically correct to define argument from month to 'month':

Maybe? Seems subjective to me. Lots of things would be more technically correct than their current implementations (I mean, imagine if every software's v1.0 was perfect in every way), but that doesn't make them easy to change, nor does that alone always provide a good motivation to consider a change at all.