This article provides a side-by-side comparison of the date and time data types in SQL Server regarding their range, accuracy, and storage size.

Data Type	Range	Accuracy	Storage Size
date	0001-01-01 through 9999-12-31	1 day	3 bytes
datetime	1753-01-01 through 9999-12-31 00:00:00 through 23:59:59.997	0.00333 seconds	8 bytes
datetime2	0001-01-01 through 9999-12-31 00:00:00 through 23:59:59.9999999	100 nanoseconds	6 to 8 bytes*
datetimeoffset	0001-01-01 through 9999-12-31 00:00:00 through 23:59:59.9999999 -14:00 through +14:00	100 nanoseconds	8 to 10 bytes*
smalldatetime	1900-01-01 through 2079-06-06 00:00:00 through 23:59:59	1 minute	4 bytes
time	00:00:00.0000000 through 23:59:59.9999999	100 nanoseconds	3 to 5 bytes*

* Note that the storage amounts listed here are the amounts listed in the Microsoft documentation. However, these data types also use 1 byte to store the precision. Therefore, add 1 byte to the amounts listed here for a more complete picture of the storage requirements.

For example, the storage size for datetime2 would range from 7 to 9 bytes if you include the extra byte.

Converting Between Data These Types

Due to the differences in accuracy and range between these data types, you need to be extra careful when converting between them. In particular, converting from a higher-precision type to a lower-precision type may result in losing part of the value, and the remaining value being rounded up.

Check out Converting Between Date & Time Data Types in SQL Server for examples.