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.

Solarwinds

SQL SERVER - Difference Between DATETIME and DATETIME2 - WITH GETDATE getdatematch

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)

Solarwinds

,
Previous Post
SQL SERVER – Difference Between DATETIME and DATETIME2
Next Post
SQL SERVER – What is Denali?

Related Posts

7 Comments. Leave new

  • Hi,
    It’s really good

    Thanks,
    Somu

    Reply
  • 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?

    Reply
    • That is not needed. Hoever you need to use distinct

      SELECT distinct * FROM #timetable WHERE firstdate = lastdate

      Reply
  • 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

    Reply
  • Wow…this is great
    thanx

    Reply
  • 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

    Reply
    • 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.

      Reply

Leave a Reply

Menu