One of the major new features in SQL Server 2008 was a revamping of the date-related data types.  In previous versions, SQL Server only offered datetime and smalldatetime.

Datetime supports dates from 1753 until 9999, with precision to approximately one quarter of one-hundredth of a second. Internally, it is stored as two four-byte integers.  The first stores the number of days before or after January 1, 1900.  The second stores the number of three hundredths (1/300) of a second since midnight.

Smalldatetime supports dates from 1900 until 2079, with precision to one minute.  Two of its four bytes store the number of days since January 1, 1900 and the other two store the number of minutes since midnight.

Now, if you do the math, you can see that both of these data types waste a certain amount of space.  Datetime uses four bytes to store a day from 1753 to 9999.  There are around three million days between 1753 and 9999, but the four bytes used to store the number of days can represent four billion (with a b) unique values.  Similarly, with smalldatetime, there are only 1440 minutes per day (60*24) and two bytes can store 65536 unique values, so obviously there is some room to store more information in four bytes than smalldatetime uses.

SQL Server 2008 introduced four new data types – time, date, datetime2, and datetimeoffset – that store and represent data more efficiently than the previously existing data types.  All of these types other than date accept an optional precision that specifies to how many decimal places the number of seconds will be stored.

Data Type Range Bytes Introduced in
time(0 … 7) 0:00:00 – 23:59:59.9999999 3-5 2008
date January 1, 1 A.D. – December 31, 9999 3 2008
smalldatetime January 1, 1900 – June 6, 2079 (precise to the nearest minute) 4 6.0
datetime January 1, 1753 – December 31, 9999 (precise to the nearest .000, .003, or .007 seconds) 8 Present in all versions
datetime2(0 … 7) January 1, 1 A.D – December 31, 9999 (precise to a number of decimal places after the second specified by the parameter) 6-8 2008
datetimeoffset(0 … 7) Same as datetime2, -14:00 – 14:00 for the offset 8-10 2008

There is one word of caution that needs to be exercised when using the more precise data types with .NET.  When using SqlConnection, all of the date times (date, smalldatetime, datetime, datetime2) are rendered as System.DateTime, which is only precise to the nearest millisecond (three decimal places).  Time is rendered as System.TimeSpan, which is accurate to the nearest “tick”, or, .0001 milliseconds.  So if you are storing data in a datetime2 and you need the extra precision in a .NET application, be sure to convert it to an appropriate type.

Anywhere in your databases where you would have used a datetime, you should now consider using one of the new types.  If you only need to store a date (e.g. birthdates), use the date type.  If you only need data to the nearest second, use datetime(0) – this will give you an object that uses less storage (six bytes instead of eight).

image credit: Calendar of the French Revolution, from Wikimedia Commons

One Comment

Comments are closed.