SQL SERVER – Difference Between DATETIME and DATETIME2

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 with this blog post today. I had asked people to honestly take part and share their view about the above two system functions.

There are few emails as well as few comments on the blog post asking a question on how did I come to know the difference between the same. The answer is from real world issues. I was called in for performance tuning consultancy, where I was asked a very strange question by one developer. Here is the situation faced by him.

System had a single table with two different columns of datetime. One column was datelastmodified and the second column was datefirstmodified. One of the columns was DATETIME and the other was DATETIME2. Developer was populating each of them with SYSDATETIME. He assumed that the value inserted in the table will be the same. This table was only accessed by INSERT statement, and there were no updates done over it in application. One fine day, he ran distinct on both these columns and was surprised by the result. He always thought that both the tables will have the same data, but in fact, they had very different data.

He presented this scenario to me. I said this is not possible, but I had to agree with him when I looked at the resultset. Here is the simple script to demonstrate the problem he was facing. This is just a sample of the original table.

You can clearly see from result that SYSDATETIME() does not populate the same value in both the fields. In fact, the value is either rounded down or rounded up in the field which is DATETIME. Eventhough we are populating the same value, the values are totally different in both the columns, thus resulting in the SELF JOIN fail and displaying of different DISTINCT values.

The best way is to use GETDATE() if you are using DATETIME, and SYSDATETIME() if you are using DATETIME2 to populate them with current date and time for accurately addressing the precision. As DATETIME2 is introduced in SQL Server 2008, the above script will only work with SQL Server 2008 and later versions.

Sorry but I don’t quite follow. I think this is normal behavior since FirstDate isn’t as precise as LastDate and so it needs to be rounded.

Situation is almost the same as converting large number like FLOAT to smaller type like INT. You need to get rid of the “extra” bits in FLOAT in order to make it fit into INT. Plus you need to round up any decimals.

Joining works if you explicitly cast LastDate to DATETIME. I’m not sure why it can’t work like that implicitly, maybe there’s some deeper reason I don’t understand. But anyway I think it’s a common problem when comparing two different data types, which vary in size, together.

Btw. Does anyone know how Sql Server gets SYSDATETIME? Does it use performance counter? I think it has to because otherwise it can’t get so precise timestamps from Windows.

Silly of me. Of course there is implicit data type conversion happening in join. It’s just from smaller data type to larger one. Now because LastDate contains more precise value (more decimals you could say) it is different from FirstDate which just gets padded by extra zeros.

HI, Correct me if I am wrong, There is another dimension of that is the precision on the datetime has 3 microseconds difference at the date change then the date searched with, I mean if I do SELECT CAST(‘2014-03-03 23:59:59.999’ AS DateTime) it returns the next day’s date, and if I do it in with ‘2014-03-03 23:59:59.998’ its returns me ‘2014-03-03 23:59:59.997’. My point is if you see the resultset and add those 3 Microseconds in DateTime2 field will make it possible to get the round figure correctly. I hope i didn’t taken you off the subject here.

I have question regarding datetime datatype.I have one view.I execute it with filter on date.But when i run it for perticular period it slows down .For ex. If I select period 1-Apr-2009 to 31-Mar-2010 then it runs very fine but if i select period 1-Mar-2010 to 31-Mar-2010 then it becomes too slow, I tried a lot to understand the problem but could not.Can you give me the explanation?

First, have you indexed the column? If not then you could gain significant performance boost from indexing.

Second, how much rows there are between 1-Apr-2009 to 31-Mar-2010 and how much rows are between 1-Mar-2010 and 31-Mar-2010? It could be that there’s a lot of rows in the periods that are causing problems and the DB has busy time reading them from the file(s).

Something you could do is to set statistics for io on and run both queries. See what results they produce. Use MSDN to analyze those results.

Hi, Check your indexes, run queries with to get the execution plan. Check what kind of seeks/scans are performed and tune your queries. Also look at the table indexes, are they right ones based on the data/user requirements.

It’s so happy to read this your blog. The issue you found in SQL server new datetime type is related time source function precision. Folks have found the root reason of the problem —–when data time values are converted (implict or explict) between different data type, which would lose some precision, so the result cannot match each other as thought. Here I would like to gave a work around solution to solve the problem which the developers met. I use it in my work.

I have a question concerning the difference between datetime and datetime2. I shall be very thankful if you could kindly give me some insights. The question is: ?? Datetime2 has larger range and more accuracy than datetime. But still datetime2 requires less storage space as compared to datetime. What is the reason for this? Best, Madhur

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and has written over 4000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.