Which one:
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?
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.