DateTime2 vs DateTime in SQL Server

Question

Which one:

• datetime
• datetime2

is the recommended way to store date and time in SQL Server 2008+?

I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only?

Answer 1

while there is increased precision with datetime2, some clients doesn't support date, time, or datetime2 and force you to convert to a string literal. Specifically Microsoft mentions "down level" ODBC, OLE DB, JDBC, and SqlClient issues with these data types and has a chart showing how each can map the type.

If value compatability over precision, use datetime

Answer 2

datetime2 wins in most aspects except (old apps Compatibility)

1. larger range of values
2. better Accuracy
3. smaller storage space (if optional user-specified precision is specified)

please note the following points

• Syntax
  • datetime2[(fractional seconds precision=> Look Below Storage Size)]
• Precision, scale
  • 0 to 7 digits, with an accuracy of 100ns.
  • The default precision is 7 digits.
• Storage Size
  • 6 bytes for precision less than 3;
  • 7 bytes for precision 3 and 4.
  • All other precision require 8 bytes.
• DateTime2(3) have the same number of digits as DateTime but uses 7 bytes of storage instead of 8 byte (SQLHINTS- DateTime Vs DateTime2)
• Find more on datetime2(Transact-SQL MSDN article)

image source : MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - Implementation and Maintenance Chapter 3:Tables -> Lesson 1: Creating Tables -> page 66