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)
7 Comments. Leave new
Hi,
It’s really good
Thanks,
Somu
Thanks for the great writeups on sqlserver, I am new to sql server while am a DB2 database expert. I just have one question,
SELECT DISTINCT a.FirstDate, b.LastDate
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate
Is there any reason why you used this instead of
SELECT * FROM #timetable WHERE firstdate = lastdate?
That is not needed. Hoever you need to use distinct
SELECT distinct * FROM #timetable WHERE firstdate = lastdate
Sir I Have table Of Leave .I Have Columns FromDate and ToDate .I Want Dates In Between From Date And ToDate .In Your Example You Have Count Of Dates
Wow…this is great
thanx
And now I’m completely confused
DECLARE @Intveral INT
SET @Intveral = 10000
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2)
WHILE (@Intveral > 0)
BEGIN
INSERT #TimeTable (FirstDate, LastDate)
VALUES (GETDATE(), SYSDATETIME())
SET @Intveral = @Intveral – 1
END
GO
SELECT COUNT(DISTINCT FirstDate) D_FirstDate, COUNT(DISTINCT LastDate) D_LastDate
FROM #TimeTable
GO
SELECT *
FROM #TimeTable
GO
DROP TABLE #TimeTable
GO
Tsyoma – You replaced the GETDATE function with SYSDATETIME and hence you are confused. This above example was inspired by using GETDATE and how useless it becomes when using higher precision.