SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE
- by pinaldave
Earlier I wrote blog post SQL SERVER – Difference Between GETDATE and SYSDATETIME which inspired me to write SQL SERVER – Difference Between DATETIME and DATETIME2. Now earlier two blog post inspired me to write this blog post (and 4 emails and 3 reads from readers).
I previously populated DATETIME and DATETIME2 field with SYSDATETIME, which gave me very different behavior as SYSDATETIME was rounded up/down for the DATETIME datatype. I just ran the same experiment but instead of populating SYSDATETIME in this script I will be using GETDATE function.
DECLARE @Intveral INT
SET @Intveral = 10000
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2)
WHILE (@Intveral > 0)
BEGIN
INSERT #TimeTable (FirstDate, LastDate)
VALUES (GETDATE(), GETDATE())
SET @Intveral = @Intveral - 1
END
GO
SELECT COUNT(DISTINCT FirstDate) D_FirstDate, COUNT(DISTINCT LastDate) D_LastDate
FROM #TimeTable
GO
SELECT DISTINCT a.FirstDate, b.LastDate
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate
GO
SELECT *
FROM #TimeTable
GO
DROP TABLE #TimeTable
GO
Let us run above script and observe the results.
You will find that the values of GETDATE which is populated in both the columns FirstDate and LastDate are very much same. This is because GETDATE is of datatype DATETIME and the precision of the GETDATE is smaller than DATETIME2 there is no rounding happening.
In other word, this experiment is pointless. I have included this as I got 4 emails and 3 twitter questions on this subject. If your datatype of variable is smaller than column datatype there is no manipulation of data, if data type of variable is larger than column datatype the data is rounded.
Reference: Pinal Dave (http://www.SQLAuthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
