CODEWITHSUNDEEP
sql-servert-sql
Panzercrisis
Panzercrisis

Reputation: 4750

What is the REAL return type of DATEADD() in SQL?

What is the real return type of DATEADD() in SQL? Microsoft's documentation here says it's a date, their documentation here says it's a datetime, and Intellisense in Management Studio says it's a smalldatetime.

There's quite a bit of difference between those, and I'm surprised Microsoft is contradicting themselves on it this much. date doesn't keep time information, and smalldatetime keeps particularly inaccurate time information; in either of those cases, you might as well forget about something like trying to keep track of the number of milliseconds that has passed.

I did try the following experiment:

select getdate(), DATEADD(ms, 150, getdate())

which reliably yields results such as:

2015-01-09 09:54:56.157,    2015-01-09 09:54:56.307

This tempts me to believe that neither of the three documented cases are true, but that it actually returns a datetime2 value instead. This is in SQL Server 2012, by the way.

So I'm just trying to confirm:

  1. Is it really a datetime2, or is it a datetime?

  2. Is this something that's quite consistent between different, relatively modern versions of SQL Server (say, since 2005)?

  3. Is there anything else to the story here that I'm not quite seeing, or am I even being thrown off by something?

So essentially this is still one question, which remains, "What is the real return type?" but the three points above kind of illustrate where I'm at on this.

EDIT

Just to note: On the first link, I was reading primarily off the top of the page, and when I saw that saying date and the other link saying datetime, I didn't feel like I could take those pages seriously enough to look for anything else that was stated. Aaron Bertrand clarified though about the differing formatting between date and date that was used in those documents, though Microsoft probably made a mistake in using italics for very non-technical terms.

Upvotes: 3

Views: 3282

Answers (1)

StuartLC
StuartLC

Reputation: 107237

From MSDN

The return data type is the data type of the date argument, except for string literals. The return data type for a string literal is datetime. An error will be raised if the string literal seconds scale is more than three positions (.nnn) or contains the time zone offset part.

So if the 3rd parameter is one of the DATE types, it will return a result of that type, e.g.

select CURRENT_TIMESTAMP, DATEADD(ms, 150, CAST(CURRENT_TIMESTAMP AS DATETIME))
select CURRENT_TIMESTAMP, DATEADD(ms, 150, CAST(CURRENT_TIMESTAMP AS DATETIME2))
select CURRENT_TIMESTAMP, DATEADD(ms, 150, CAST(CURRENT_TIMESTAMP AS SMALLDATETIME))

Returns 3 different types, viz DateTime, DateTime2 and SmallDateTime respectively.

Upvotes: 3

Related Questions