On the Advantages of DateTime2(n) over DateTime

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:

declare @datetime varchar(50) = '01/01/2012 11:11:11.1111111'

select        @datetime

select        convert(datetime2(0), @datetime)

select        convert(datetime2(1), @datetime)

select        convert(datetime2(3), @datetime)

select        convert(datetime2(4), @datetime)

select        convert(datetime2(7), @datetime)

select        convert(datetime2, @datetime) --default is 7

Want to do the same conversion with datetime or smalldatetime?  Can't.

Msg 241, Level 16, State 1, Line 10

Conversion failed when converting date and/or time from character string.

The old data types can't handle that much precision.  Gotta dial it down for the old datetime and smalldatetime types.  How quaint.

declare @datetime varchar(50) = '01/01/2012 11:11:11.111'

select        convert(datetime, @datetime)

select        convert(smalldatetime, @datetime)

select        convert(datetime2(0), @datetime)

select        convert(datetime2(1), @datetime)

select        convert(datetime2(3), @datetime)

Note how the old data types are incapable of storing precision out to one one-thousandth of a second.

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.

Storage requirements 

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.

Note
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.

.net developers?  Datetime and Datetime2(n) both map to System.DateTime.  No worries there.  More info here: http://msdn.microsoft.com/en-us/library/bb675168.aspx

HOWEVER, you should probably be using datetimeoffset, not just datetime2. DateTimeOffset works similarly to datetime2, but adds 2 bytes to store a +-hh:mm data. While this doesn't store the time zone (CDT, EST, etc.) it does store the offset. I've given a joint presentation on this topic with a colleague that goes into more detail. (update 7/31/2017)