Starting with SQL 2008, we database developers started becoming more familiar with datetime2. Sometimes folks need convincing though, so here goes.
Here's a brief review of how the precision of the datetime2 data type converts from a varchar representing a time value out to one ten-millionths of a second. Run this script yourself or view the results in the image below:
Want to do the same conversion with datetime or smalldatetime? Can't.
How about date ranges?
datetime: 1753-01-01 through 9999-12-31
smalldatetime: 1900-01-01 through 2079-06-06
datetime2: 0001-01-01 through 9999-12-31
Now the kicker. What's the cost to storing all that extra precision in datetime2? None. You can get more precision than datetime and fewer bytes per row per field by specifying a precision value for columns declared as datetime2(n).
For example, datetime(2) stores one hundreds of a second - realistically the same precision as datetime, which rounds the third place to the right of the decimal. And datetime(2) is two bytes smaller than datetime, making it ideal.
Don't need seconds, just hours and minutes? Stick with smalldatetime, 4 bytes, as opposed to datetime2(0) at 6 bytes.
smalldatetime:
4 bytes - precision to the minute (seconds are always :00)
datetime2(n):
6 bytes for precisions less than 3 - precision up to one hundredth of a second
7 bytes for precisions 3 and 4 - precision up to one ten thousandth of a second
8 bytes for precisions > 4 - precision up to one ten millionth of a second (within 100 nanoseconds)
datetime:
8 bytes - precision to one hundredth of a second, rounded precision to three thousands of a second
Clearly, datetime2 is an upgrade in range of values, precision (no rounding!) and storage size over datetime.
And that's only if you need to store date and time info. Since SQL 2008, we've also been able to store mm/dd/yyyy data in the date data type (3 bytes), and discrete hh:mm:ss in the time data type (5 bytes).
Oh yeah, and even though datetime is not deprecated, this friendly yellow box might make you think so.
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. |
Note
