DateTime2 vs DateTime in SQL Server

The MSDN documentation for datetime recommends using datetime2. Here is their recommendation:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage.

I concurr with @marc_s and @Adam_Poward -- DateTime2 is the preferred method moving forward. It has a wider range of dates, higher precision, and uses equal or less storage (depending on precision).

One thing the discussion missed, however...
@Marc_s states: Both types map to System.DateTime in .NET - no difference there. This is correct, however, the inverse is not true...and it matters when doing date range searches (e.g. "find me all records modified on 5/5/2010").

.NET's version of Datetime has similar range and precision to DateTime2. When mapping a .net Datetime down to the old SQL DateTime an implicit rounding occurs. The old SQL DateTime is accurate to 3 milliseconds. This means that 11:59:59.997 is as close as you can get to the end of the day. Anything higher is rounded up to the following day.

Try this :

declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'

Avoiding this implicit rounding is a significant reason to move to DateTime2. Implicit rounding of dates clearly causes confusion:

• Strange datetime behavior in SQL Server
• http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
• SQL Server 2008 and milliseconds
• http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspx
• http://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx

Old Question... But I want to add something not already stated by anyone here... (Note: This is my own observation, so don't ask for any reference)

Datetime2 is faster when used in filter criteria.

TLDR:

In SQL 2016 I had a table with hundred thousand rows and a datetime column ENTRY_TIME because it was required to store the exact time up to seconds. While executing a complex query with many joins and a sub query, when I used where clause as:

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

The query was fine initially when there were hundreds of rows, but when number of rows increased, the query started to give this error:

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

I removed the where clause, and unexpectedly, the query was run in 1 sec, although now ALL rows for all dates were fetched. I run the inner query with where clause, and it took 85 seconds, and without where clause it took 0.01 secs.

I came across many threads here for this issue as datetime filtering performance

I optimized query a bit. But the real speed I got was by changing the datetime column to datetime2.

Now the same query that timed out previously takes less than a second.

cheers

I just stumbled across one more advantage for DATETIME2: it avoids a bug in the Python adodbapi module, which blows up if a standard library datetime value is passed which has non-zero microseconds for a DATETIME column but works fine if the column is defined as DATETIME2.

As the other answers show datetime2 is recommended due to smaller size and more precision, but here are some thoughts on why NOT to use datetime2 from Nikola Ilic:

• lack of (simple) possibility to do basic math operations with dates, like GETDATE()+1
• every time you are doing comparisons with DATEADD or DATEDIFF, you will finish with implicit data conversion to datetime
• SQL Server can’t use statistics properly for Datetime2 columns, due to a way data is stored that leads to non-optimal query plans, which decrease the performance

I think DATETIME2 is the better way to store the date, because it has more efficiency than the DATETIME. In SQL Server 2008 you can use DATETIME2, it stores a date and time, takes 6-8 bytes to store and has a precision of 100 nanoseconds. So anyone who needs greater time precision will want DATETIME2.

Accepted answer is great, just know that if you are sending a DateTime2 to the frontend - it gets rounded to the normal DateTime equivalent.

This caused a problem for me because in a solution of mine I had to compare what was sent with what was on the database when resubmitted, and my simple comparison '==' didn't allow for rounding. So it had to be added.

datetime2 is better

• datetime range : 1753-01-01 through 9999-12-31 , datetime2 range : 0001-01-01 through 9999-12-31

• datetime Accuracy : 0.00333 second , datetime2 Accuracy : 100 nanoseconds

• datetime get 8 bytes , datetime2 get 6 to 8 bytes depends on precisions

  (6 bytes for precision less than 3 , 7 bytes for precision 3 or 4 , All other precision require 8 bytes, Click and Look at the below picture)