What is the REAL return type of DATEADD () in SQL?
What is the real return type DATEADD()
in SQL? Microsoft's documentation here says it is date
, their documentation here says it is datetime
, and the Intellisense in Management Studio says it is smalldatetime
.
There are so many differences between the two and I'm surprised Microsoft contradicts this so much. date
does not store information about time, but smalldatetime
stores especially inaccurate information about time; in any of these cases, you can also forget about something like trying to keep track of the number of milliseconds passed.
I tried the following experiment:
select getdate(), DATEADD(ms, 150, getdate())
which reliably gives results like:
2015-01-09 09:54:56.157, 2015-01-09 09:54:56.307
This leads me to think that none of the three documented cases are correct, but it returns a value instead datetime2
. This is in SQL Server 2012 by the way.
So, I'm just trying to confirm:
-
Is this really
datetime2
, or is itdatetime
? -
Is this something quite consistent across different, relatively modern versions of SQL Server (say, from 2005)?
-
Is there anything else in this story that I can't quite see, or is it even throws me off?
So essentially, this is another question that remains: "What is the real return type ?" but three points higher than illustrate where I am on this.
EDIT
Just to point out, the first link I read primarily from the top of the page, and when I saw this saying date
and the other link that said datetime
I didn't feel like I could these pages seriously enough to look for anything else that was said. Aaron Bertrand clarified, however, about the different formatting between date and date that was used in these documents, although Microsoft probably made a mistake in using italics for very non-technical terms.
source to share
The return data type is the data type of the date argument, excluding string literals. The return type for a string literal is date and time. The error will escalate if the literal scale of rows is greater than three positions (.nnn) or contains part of the time zone offset.
So, if the third parameter is one of the types DATE
, it will return the result of that type, eg.
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, respectively DateTime, DateTime2 and SmallDateTime
.
source to share