SQL Server Magazine

Date and time manipulation is integral to most applications. Until SQL Server 2008, SQL Server didn’t have separate data types for date and time. SQL Server 2008 not only provides separate DATE and TIME data types but also introduces other date- and time-related data types, new functions, and enhancements to existing functions to support the new data types. In this article I explore the new date- and time-related data types and functions, and I cover a few issues that are important to consider regarding compatibility with older data types.

Date and Time Data Types
Prior to SQL Server 2008 we had two date- and time-related data types—DATETIME and SMALLDATETIME. These types have several limitations. Probably the biggest limitation is that they contain both date and time components that are inseparable. If you need to store only dates, the common approach is to always store the values at midnight; if you need only time, you store all values at the base date, January 1, 1900. Anyway you look at it, it’s awkward. Also, when you need to store only dates or only times, you still pay the full storage requirements (8 bytes for DATETIME, and 4 bytes for SMALLDATETIME). The precision of DATETIME is 3.333 milliseconds. For some systems, that’s not enough. Also, the range of dates supported by DATETIME starts with January 1, 1753. For systems that need to store older dates, DATETIME isn’t useful. Finally, the older data types have no time zone support.

SQL Server 2008 introduces four new data types that address most of these limitations. Table 1 lists all date- and time-related data types that are supported in SQL Server 2008, including the old ones. For each data type, Table 1 provides the storage in bytes, supported date range, precision, and recommended entry format.

As you can see in Table 1, the DATE data type requires only 3 bytes of storage, and TIME requires 3 to 5 bytes. That’s a big savings compared with DATETIME. With the TIME data type (and the other new types that contain a time component: DATETIME2 and DATETIMEOFFSET), you can define the precision in terms of a fraction of a second (hence the storage is expressed as a range). You do so by specifying a number in the range 0 through 7 in parentheses after the type name, as in TIME(3). The value 0 means a whole second accuracy, 3 means millisecond accuracy, and 7 (the default) means 100 nanoseconds accuracy. Note that 7 is the default, so bear in mind that if you don’t specify a value, you will pay the maximum storage requirement for the type. So as a best practice, I recommend always specifying the value based on your actual needs, rather than leaving it with the default 7.

The new data types that contain a date component (DATE, DATETIME2, and DATETIMEOFFSET) support dates starting with the year 0001. So systems that need to keep track of dates prior to 1753 can now do so.

As you probably figured, the DATETIME2 data type is simply a merge of the new DATE and TIME data types. You can think of it as an improved version of the older DATETIME data type. (During the prerelease period of SQL Server 2008, I remarked to some SQL Server MVPs and Microsoft folks that I found the name DATETIME2 amusing, and I jokingly suggested using DATETIMETOO instead. One of the other SQL Server MVPs then suggested enhancing the name to DATETIMETOOLATE, and yet another suggested DATETIMETOOMUCH. Unfortunately, none of our witty suggestions ultimately made the final release.)

The DATETIMEOFFSET data type gives you all the functionality you get from DATETIME2, plus a time zone component. Unfortunately, though, it doesn’t have a daylight savings component. Courtesy of SQL Server MVP Steve Kass, an important note about working with the DATETIMEOFFSET data type is the way the DATEDIFF function behaves when operating on values of this type. First, without looking at the documentation, try to predict the result of the following expression:

SELECT

  DATEDIFF(day, 

    '2009-02-12 12:00:00.0000000 -05:00', 

    '2009-02-12 22:00:00.0000000 -05:00') AS days;