When it comes to a choice between data types for a field in a SQL Server database's table, an issue that is frequently discussed in popular forums is a choice between the DATETIME and DATETIME2 data types. According to the official MSDN documentation, it is recommended that you use DATETIME2 for new work because it is more portable, aligns with the SQL Standard, offers more precision and has a greater range. There aren't too many people who would dispute the recommendations of one of the "Big 4" companies, myself included, but, for those curious minds out there, let's see why DATETIME2 is the better choice.
Precision
DATETIME2 has a fractional precision of up to 7 digits compared to the DATETIME's precision of 3 fractional digits. The 'up to' part means that the user can manually specify the precision through an optional parameter. The default precision is 7 digits. This increased precision means that a conversion to the DATETIME2 data type of a string like '2016-11-11 20:20:20.4444' will succeed whereas the conversion of the same string to DATETIME will fail.
Accuracy
DATETIME2 supersedes DATETIME in accuracy by a relatively big margin. Although DATETIME has a precision of 3 fractional digits, it will round the last digit to an increment of .000, .003 or .007 whereas the DATETIME2 data type, supports an accuracy of 100 nanoseconds. Let's see how these differences affect the values by converting '2016-11-11 20:20:20.444' to DATETIME and DATETIME2 with 3 digits of precision.
Even though the conversion is supported by both data types, converting to DATETIME means that you will be sacrificing accuracy. So if you aim to accurately store date and time with more than 2 fractional digits in your database the only choice for the data type is DATETIME2.
Range
DATETIME2 also supports a greater range of values than DATETIME. The former supports dates from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.9999999 whereas the latter supports dates from 1753-01-01 00:00:00 to 9999-12-31 23:59:59.997. As a small additional benefit that avoids some confusion for those developers working with the .NET platform, the range of DATETIME2 complies with the range of the DateTime data type in C# and VB.NET.
Memory space required
If you are thinking that the additional capabilities of the DATETIME2 data type translate into additional storage space requirements, you are mistaken. DATETIME2 requires anywhere between 6 and 8 bytes whereas DATETIME requires 8 bytes of storage. The space required by DATETIME2 depends on the fractional precision you choose for the column:
- 0 to 2 digits - 6 Bytes
- 3 to 4 digits - 7 Bytes
- more than 4 digits - 8 Bytes
So if your aim is to save storage space and increase read performance, DATETIME2 is the way to go.
Compliance with standards
DATETIME2 is compliant with both the ANSI and ISO 8601 standards for SQL whereas DATETIME is not compliant with any of those standards.
In conclusion, if it's range, precision, accuracy, storage space optimization or compliance with standards that you require, DATETIME2 is a better choice.
