SQL Server: DateTime vs DateTime2

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes. Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to microseconds and avoids the 3/1000 second rounding issue. The precision with a datetime2 is upto 100 nanoseconds.

Here’s an example:



OUTPUT

As you can see, when using the datetime datatype is rounded to increments of .000, .003, or .007 seconds. However the datetime2 has a larger date range, a larger default fractional precision, and optional user-specified precision. The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds. The default precision is 7 digits.

Moreover datetime2 supports a date range of 0001-01-01 through 9999-12-31 while the datetime type only supports a date range of January 1, 1753, through December 31, 9999. The timerange as mentioned earlier in case of datetime is 00:00:00 through 23:59:59.997 whereas in datetime2 is 00:00:00 through 23:59:59.9999999.

The MSDN documentation also recommends datetime2 over datetime:

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.