SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

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)

About these ads

10 thoughts on “SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

  1. 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?

  2. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – Information Related to DATETIME and DATETIME2 Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

  5. 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

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s