Much Ado About Dates

I just did a short article for the PASS newsletter on SQL Server 2008 and date/time types. I thought I'd copy it here too: 

SQL Server 2008 introduces a wave of changes in the way we use and process dates and times in SQL Server. Reportedly, having separate date and time data types was one of the highest-rated customer requests from previous versions. The early betas of SQL Server 2005 included a separate date data type but this was withdrawn from the release when a number of problems with the way it was implemented became apparent. Thankfully, this has returned in SQL Server 2008 along with a host of other date/time improvements.

At the time it was introduced, the precision of the datetime data type no doubt seemed appropriate. It offered a range of January 1^st 1753 through December 31^st, 9999 with an accuracy of 3.33 milliseconds. The reason for the odd start date was the alignment of the Julian and Gregorian calendars just prior to that time. So while writing a date involving a year like 1152 might seem simple, the date arithmetic required could become quite complicated and involve knowledge of which country was being discussed as changes occurred in different countries at different times.

It could also be argued that dates well into the future might offer some potential for change as well. I gather that scientists are still discussing whether or not the years 4000 and 8000 should or shouldn’t be leap years.I’m sure it’s important that they discuss that but I would have to say that I’m not too concerned about the outcome, either way J

While it might seem fairly pointless to deal with dates earlier than 1753, it can create a source of errors when migrating data between systems. As an example, I was recently porting some data from an Oracle database. An error occurred because the Oracle system included a date with the year 200. Even though this was an error (it was meant to be 2000), it was enough to cause a problem as SQL Server 2005 had a narrower range of dates than the source system.

The new datetime2 data type now comes to the rescue. It has a range of 1^st January 0001 through 31^st December 9999 with a precision of 7 decimal places ie: 100 nanoseconds. The name of the data type has been the source of a lot of discussion. Many of us would have preferred perhaps bigdatetime, in keeping with other existing SQL Server data types like bigint but the name chosen does align well with common practice in the .NET development arena.

Several literal string formats are supported for the datepart. Numeric options are mm/dd/yyyy, dd-m-yyyy and mm.dd.yyyy. An alphabetical option of mon dd, yyyy is supported along with ISO 8601 formats of yyyy-mm-dd and yyyymmdd, an ODBC format of {d’yyyy-m-dd’} and finally a W3XML format of yyyy-mm-ddZ. It is important to note that it is unaffected by dateformat set options, unlike the existing datetime data type.

Datetime2 is also a system CLR data type. In SQL Server 2008, many of the new data types are implemented as system CLR types. Regardless of whether or not CLR integration is enabled at the system level, these types are available.

The next new data type is the date data type. This is identical to the date portion of the new datetime2 data type and uses the same literal string formats. Developers have called for this type for a long time as systems often store datetime values but only want to search on a date. For example, on a table with a TransactionDateTime column (holding both date and time), we might often want to locate all the rows on a particular date. In SQL Server 2005, the approach was to use a clause like:

WHERE TransactionDateTime >= @RequiredDate AND TransactionDateTime < @TheDayAfter

A common mistake was to try to wrap the column in a function such as:

WHERE CONVERT(date,TransactionDateTime) = @RequiredDate

This, however, caused performance problems as an index on the TransactionDateTime column could well be considered not useful by the query optimizer. SQL Server 2008 comes to the rescue on this in two ways. First, we can now have a column that just holds a date. Second, the pattern involving the CONVERT function is now specifically detected and index searchability on such existing datetime columns has been restored. The date data type is stored in a fixed three byte location.

The new time data type offers a variable level of precision. Declaring a variable using it should normally involve a decision on the degree of precision required. For example:

DECLARE @When Time(6)

This would declare a variable with up to six decimal places for the seconds part of the time component. The maximum precision value is 7, which indicates 100ns.

The time data type is stored in a location of between three and five bytes. It does not have a fixed size.

Although this was all great work, the SQL Server 2008 team haven’t stopped there. Great support for timezones has been added to the product as well. The DateTimeOffset data type is similar to the new DateTime2 data type but also adds support for storing the timezone offset. This means we can now store values such as:

2007-08-13 10:30:39.432343 -07:00

The timezone offset is preserved and returned. All comparisons of the DateTimeOffset data type are performed as UTC (universal time aka GMT). This affects comparing values, sorting values and indexing them.

SQL Server 2008 also provides functions that allow processing timezone offsets. The SWITCHOFFSET function allows modifying the timezone offset of a DateTimeOffset value while preserving the actual time (ie: UTC-based time).

With support for higher precision and offsets comes the need to enhance the system functions for date and time. In particular, the GETDATE function is now deprecated and replaced by the higher precision SYSDATETIME function. SYSDATETIMEOFFSET provides a value with the timezone offset of the server and SYSUTCDATETIME provides the current UTC time.

The changes and enhancements to date and time management in SQL Server 2008 are a very welcome addition to the product, should provide a great basis for the future and assist today in interoperability with other systems.