When working with SQL Server, it is important to understand the different date and time functions available and how they can affect your data. In a previous blog post, we discussed the difference between the GETDATE and SYSDATETIME functions. Today, we will explore the difference between the DATETIME and DATETIME2 data types.

In a recent experiment, I populated a DATETIME and DATETIME2 field with the GETDATE function. The results were interesting and shed light on the behavior of these data types.

Let’s take a look at the code:

DECLARE @Interval INT
SET @Interval = 10000

CREATE TABLE #TimeTable (
    FirstDate DATETIME,
    LastDate DATETIME2
)

WHILE (@Interval > 0)
BEGIN
    INSERT INTO #TimeTable (FirstDate, LastDate)
    VALUES (GETDATE(), GETDATE())

    SET @Interval = @Interval - 1
END

SELECT COUNT(DISTINCT FirstDate) AS D_FirstDate, COUNT(DISTINCT LastDate) AS D_LastDate
FROM #TimeTable

SELECT DISTINCT a.FirstDate, b.LastDate
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate

SELECT * FROM #TimeTable

DROP TABLE #TimeTable

After running the above script, we can observe that the values of GETDATE, which are populated in both the FirstDate and LastDate columns, are very similar. This is because GETDATE is of the DATETIME data type, and the precision of GETDATE is smaller than DATETIME2, so no rounding occurs.

It is important to note that if the data type of a variable is smaller than the column data type, there will be no manipulation of data. However, if the data type of a variable is larger than the column data type, the data will be rounded.

While this experiment may seem pointless, it addresses a common question that I received from readers. Understanding the behavior of different date and time functions can help you make informed decisions when working with SQL Server.

Stay tuned for more articles on SQL Server concepts and ideas!