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.
DATETIME2 has a date range of “0001 / 01 / 01” through “9999 / 12 / 31” while the DATETIME type only supports year 1753-9999.
Also, if you need to, DATETIME2 can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns.
Both types map to System.DateTime in .NET – no difference there.
If you have the choice, I would recommend using DATETIME2 whenever possible. I don’t see any benefits using DATETIME (except for backward compatibility) – you’ll have less trouble (with dates being out of range and hassle like that).
Plus: if you only need the date (without time part), use DATE – it’s just as good as DATETIME2 and saves you space, too! 🙂 Same goes for time only – use TIME. That’s what these types are there for!