Yesterday, I wrote a blog post discussing the difference between GETDATE and SYSDATETIME in SQL Server, and it received a tremendous response. If you haven’t read that post yet, I highly recommend doing so before continuing with this one.

In that post, I asked readers to share their views on these two system functions, and I received several emails and comments asking how I came to know about the difference. The answer is simple – real-world issues. I was recently called in for a performance tuning consultancy, where I encountered a developer facing a peculiar problem.

The system had a single table with two different datetime columns – “datelastmodified” and “datefirstmodified”. One column was of type DATETIME, while the other was of type DATETIME2. The developer was populating both columns with SYSDATETIME, assuming that the values inserted would be the same. This table was only accessed by INSERT statements, and no updates were performed on it in the application.

However, one day the developer ran a DISTINCT query on both columns and was surprised by the result. He always believed that both columns would have the same data, but in reality, they had very different data. He presented this scenario to me, and initially, I thought it was not possible. But when I looked at the result set, I had to agree with him.

Here is a simplified script to demonstrate the problem he was facing:

DECLARE @Intveral INT
SET @Intveral = 10000

CREATE TABLE #TimeTable (
    FirstDate DATETIME,
    LastDate DATETIME2
)

WHILE (@Intveral > 0)
BEGIN
    INSERT #TimeTable (FirstDate, LastDate)
    VALUES (SYSDATETIME(), SYSDATETIME())

    SET @Intveral = @Intveral - 1
END

SELECT COUNT(DISTINCT FirstDate) AS D_GETDATE, COUNT(DISTINCT LastDate) AS D_SYSGETDATE
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

When you run this script, you can clearly see that SYSDATETIME() does not populate the same value in both fields. In fact, the value is either rounded down or rounded up in the field that is of type DATETIME. Even though we are populating both columns with the same value, the values are completely different, resulting in the failure of the self-join and the display of different DISTINCT values.

The best practice is to use GETDATE() if you are using DATETIME, and SYSDATETIME() if you are using DATETIME2 to accurately address the precision when populating them with the current date and time. It’s important to note that DATETIME2 was introduced in SQL Server 2008, so the above script will only work with SQL Server 2008 and later versions.

I hope this post answers some of the questions asked in the previous blog post. Understanding the differences between these system functions can help avoid unexpected results and ensure accurate data manipulation in your SQL Server databases.