SQL Server 2008 Datatypes and the Death of Datetime

Beginning with SQL Server 2008, the datatypes which store date and time were greatly improved. Despite this, many legacy databases (and often newly developed ones) still use Datetime and SmallDatetime, the original datatypes. Aside from the dwindling possibility of backwards compatibility issues, there is no advantage to using these old datatypes; as this article will show, the new implementations are superior in every way.

SQL 2005 and Earlier: Datetime and SmallDatetime

Datetime and SmallDatetime are the original temporal SQL Server datatypes, and the only options available in SQL Server 2005 and earlier. During this time, SQL Server date support was widely criticized for its lack of options. Other database systems, such as Oracle, already featured individual datatypes for date and time, as well as specific functionality for time zones.

SmallDatetime was designed to consume less space at the cost of accuracy. Its to-the-minute rounding caused it to be sparsely utilized.

Neither Datetime nor SmallDatetime are currently recommended for use in new SQL Server developments. Instead, Datetime2 is the standard for storing date and time information in a single field; as we’ll see, this method has virtually no downsides.

SQL 2008 and Beyond: Datetime2, Date, and Time

Datetime2 is the replacement for Datetime. It’s an improvement on every aspect of Datetime – storage space, options, and precision are all handled more effectively.

A single parameter – precision, or the number of decimal places – is all that Datetime2 requires. The default number of decimal places is 7, but the accepted number can be anywhere from 0 to 7.

The amount of storage space required for Datetime2 datatypes is based on the number of specified decimal places. Thus, datatypes with a precision of 0-2 occupy 6 bytes; precisions between 3 and 4 occupy 7 bytes; anything above 4 decimal places consumes the full 8 bytes.

Even with a maximum precision of 100 nanoseconds, Datetime2 consumes the same storage space as Datetime, which has a precision of only 3.3ms.

The following set of examples demonstrates Datetime2’s flexibility and efficiency.

Examples of Differing Precisions with Datetime2

-- Precision of 0
DECLARE @NewDateTime0 Datetime2(0)

-- Default Precision (7)
DECLARE @NewDateTime Datetime2

-- Precision of 2
DECLARE @NewDateTime2 Datetime2(2)

Set @NewDateTime0 = CONVERT(Datetime2, '2015-01-01 12:54:32.343433')
Set @NewDateTime = CONVERT(Datetime2, '2015-01-01 12:54:32.343433')
Set @NewDateTime2 = CONVERT(Datetime2(3), '2015-01-01 12:54:32.343433')

Select @NewDateTime0 as Precision0
Select @NewDateTime as Precision7
Select @NewDateTime2 as Precision3

Both Datetime and Datetime2 map to System.Datetime in .NET.

Date, also introduced in SQL Server 2008, ignores the time component and only stores date information. It’s a lean 3 bytes, and it is very useful for sorting dates exclusive of time. This is particularly helpful in reporting, where sorting by individual days becomes much easier and efficient.

Similarly, Time ignores the date component. It is based on a 24-hour clock, and has no time zone functionality.

Like Datetime2, Time takes a single parameter – precision. This parameter determines the column length, precision and scale, as noted on MSDN:

Datetimeoffset: SQL Server 2008’s Most Powerful Datatype

Datetimeoffset is the most powerful datatype introduced in SQL Server 2008. Ranging from 8-10 bytes, it allows for the storage of date, time, and regional time zone data.

-- January 2nd, 2015, 12 Noon - USA, EST (New York), standard non-DST offset
DECLARE @datetimeoffset datetimeoffset(4) = '2015/01/02 12:00:00 -05:00';
Select @datetimeoffset

The offset of -05:00 indicates the record’s date and time is specific to the Eastern U.S./New_York timezone.

The SQL function SysDateTimeOffset returns the date, time, and time zone of the system on which the function is executed.

Regionally identifying information is tremendously helpful in globally distributed databases: it simplifies reporting and prevents the need for conversions into another time zone for analysis.

For example, airport flight data often crosses regional boundaries. Using Datetime for departure and arrivals is not nearly enough to know, worldwide, the local time of each flight. It’s also necessary to know what regions or time zones the flight crosses. Even then, additional logic will be needed to do the time zone conversions, so this is only a partial solution:

Note: This model uses SQL Server 2005 datatypes. It requires additional columns and logic, and is not recommended.

Using Datetimeoffset eliminates the above problem. The region and time zone data are stored directly in the field, so there is no ambiguity.

Note: This model uses SQL Server 2008+ datatypes, simplifying the storage and logic needed.

The biggest weakness in Datetimeoffset is the lack of support for daylight savings time. Currently, the most common solution is to use a conversion table. An example of this can be found on MSSQLtips. It requires the pre-population of date ranges for each time zone.

Conclusion: It’s Time to Retire Datetime and SmallDatetime

All new development should use Date, Time, Datetime2, and Datetimeoffset.

Although Datetime and SmallDatetime have not been officially deprecated by Microsoft, I strongly recommend eliminating their usage. Datetime2 has essentially no disadvantages when compared to Datetime. In fact, its ability to separate date and time into individual fields can be greatly beneficial. Datetimeoffset provides a powerful, simple alternative to handling region offsets programmatically.