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.

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

I hope I have answered some of the questions asked yesterday.

Reference: Pinal Dave (http://www.SQLAuthority.com)

About these ads

20 thoughts on “SQL SERVER – Difference Between DATETIME and DATETIME2

  1. Hi,

    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.

      Funny what comes to mind when watching cartoons at morning :)

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

      Thanks

  2. Hello sir

    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.

      http://msdn.microsoft.com/en-us/library/ms184361.aspx

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

  3. Good points on what kind of date functions to use with DATETIME and DATETIME2 datatypes. Looks like really time sensitive applications/data could benefit from DATETIME2 data type.

  4. Pingback: SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE Journey to SQL Authority with Pinal Dave

  5. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  6. hi Pinal

    I have executed the same on sql server 2008 and found that

    1- First query returned same results for both columns. Results is below:-

    D_GETDATE D_SYSGETDATE
    84 84

    2- Joins are also working fine.
    FirstDate LastDate
    2010-07-07 06:55:32.527 2010-07-07 06:55:32.5270000
    2010-07-07 06:55:34.777 2010-07-07 06:55:34.7770000
    2010-07-07 06:55:34.027 2010-07-07 06:55:34.0270000

    Thanks.
    Ashutosh

  7. Hi Pinal,

    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.

    Hopes it helps to the developers met same problem.

    Jing

    —————————————————————

    DECLARE @Intveral INT, @CurDate DATETIMEOFFSET;
    CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2, GlobalDate DATETIMEOFFSET)

    SET @Intveral = 10000
    WHILE (@Intveral > 0)
    BEGIN

    –SET @CurDate = SYSDATETIMEOFFSET(); – higher precision for future use only
    SET @CurDate = TODATETIMEOFFSET(GETDATE(),DATEDIFF(N,GETUTCDATE(),GETDATE())); – lower precision to match exited date process

    INSERT #TimeTable (FirstDate, LastDate, GlobalDate)
    VALUES (@CurDate, @CurDate, @CurDate)
    SET @Intveral = @Intveral – 1
    END
    GO

    SELECT COUNT(DISTINCT FirstDate) D_DATETIME,
    COUNT(DISTINCT LastDate) D_DATETIME2,
    COUNT(DISTINCT GlobalDate) D_SYSGETDATE
    FROM #TimeTable
    GO

    SELECT DISTINCT a.FirstDate,b.LastDate, b.GlobalDate,CAST(b.GlobalDate AS DATETIME)
    FROM #TimeTable a
    INNER JOIN #TimeTable b ON a.FirstDate =CAST (b.GlobalDate AS DATETIME)
    GO

    SELECT *
    FROM #TimeTable
    GO

    DROP TABLE #TimeTable
    GO

  8. Pingback: SQL SERVER – Information Related to DATETIME and DATETIME2 Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s