Which sql-server date-time type to use?

Sql Server offers several datetime types. Here I will offer quick&simple guidelines on how to pick the right one for your specific need.
The basic idea is that we want maximum precision for minimal storage space:

• If all you need is accuracy by date, irregardless of time, use the date type. It uses 3 bytes.
• If you need date + rough time value (accuracy level - minutes), use can the smalldatetime type. It uses 4 bytes. However there is another point to take into consideration here - Microsoft doesn't recommend using smalldatetime, quote: "Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable." So you should use smalldatetime only if the storage benefit (2 bytes less than the next best option) really justifies using a less portable type.
• The next type of choice is datetime2. This type completely replaces the datetime type - it's more accurate, more sql-compliant, and can be configured to use less storage space. While datetime always uses 8 bytes, datetime2 can use 6, 7 or 8 bytes, depending on the requested precision (1-7, which indicates how many digits are kept for fractions of a second)
• The best precision you can get for 6 bytes is 2 digits for seconds fractions (i.e. hundreds of a second - such as 2017-02-05 08:55:39.83). If this precision is enough for you - specify datetime2(2) as your type.
• The best precision you can get for 7 bytes is 4 digits for seconds fractions (for example: 2017-02-05 08:59:24.1733). So if this precision is enough for you, specify datetime2(4) as your type.
• If the latter still doesn't meet your need, than you must use 8 bytes, The best precision you can get here is 7 digits for seconds fractions (example: 2017-02-05 09:01:57.9833333). In this case, specify datetime2(7) as your type. 

Note that datetime2(7) is the most precise date-time type Sql Server offers. As comparison, the datetime type offers only 3 digits for second fractions, while datetime2(7) offers 7, So there is no more need to use the datetime. In fact, even datetime2(4) is more precise than datetime, while it only uses 7 bytes instead of 8.

There is another Sql Server date-time type - datetimeoffset. It's beyond the scope of this post. In essence, it's composed of datetime2 + an additional indication of local time (offset from UTC). It may be useful in multi time-zone scenarios.

Adam Porat is a senior c# developer at Travolutionary.