SQL SERVER – Difference Between DATETIME and DATETIME2
- by pinaldave
Yesterday I have written a very quick blog post on SQL SERVER – Difference Between GETDATE and SYSDATETIME and I got tremendous response for the same. I suggest you read that blog post before continuing this blog post today. I had asked people to honestly take part and share their view about above two system function.
There are few emails as well few comments on the blog post asking question how did I come to know the difference between the same. The answer is real world issues. I was called in for performance tuning consultancy where I was asked very strange question by one developer. Here is the situation he was facing.
System had a single table with two different column of datetime. One column was datelastmodified and second column was datefirstmodified. One of the column was DATETIME and another was DATETIME2. Developer was populating them with SYSDATETIME respectively. He was always thinking that the value inserted in the table will be the same. This table was only accessed by INSERT statement and there was no updates done over it in application.One fine day he ran distinct on both of this column and was in for surprise. He always thought that both of the table will have same data, but in fact they had very different data.
He presented this scenario to me. I said this can not be possible but when looked at the resultset, I had to agree with him. Here is the simple script generated to demonstrate the problem he was facing. This is just a sample of original table.
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
GO
SELECT COUNT(DISTINCT FirstDate) D_GETDATE, COUNT(DISTINCT LastDate) D_SYSGETDATE
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 see the resultset.
You can clearly see from result that SYSDATETIME() does not populate the same value in the both of the field. In fact the value is either rounded down or rounded up in the field which is DATETIME. Event though we are populating the same value, the values are totally different in both the column resulting the SELF JOIN fail and display different DISTINCT values.
The best policy is if you are using DATETIME use GETDATE() and if you are suing DATETIME2 use SYSDATETIME() to populate them with current date and time to accurately address the precision. As DATETIME2 is introduced in SQL Server 2008, above script will only work with SQL SErver 2008 and later versions.
I hope I have answered few questions asked yesterday.
Reference: Pinal Dave (http://www.SQLAuthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
