In SQL Server 2008 we have a date-only data type, i.e. no time element. We also have a time-only data type, i.e. no date element. SQL Server 2008 has a new data type with time offset – commonly used to accommodate international time zones. SQL Server 2008 also has a new data type with a wider date range. If in SQL Server 2005 the minimum date is 1st January 1753, in SQL Server 2008 we have a new data type with minimum date 1st January 0001.

In this short article we will explore date and time data types in the coming SQL Server 2008, to understand the improvements from SQL Server 2005 and 2000. We will also discuss date and time functions. It's true that SQL Server 2008 is still months away (end of Feb), but as they say, it is never too early to know.

In SQL Server 2008 we have 6 date and time data types:

1. datetime
2. smalldatetime
3. date
4. time
5. datetime2
6. datetimeoffset

The first two are the same as in SQL Server 2000 and 2005. "datetime" stores both date and time components, ranging from 1st January 1753 to 31st December 9999, with accuracy of 3.33 milliseconds, for example: "2007-10-14 19:35:09.333". "smalldatetime" also stores both date and time components, ranging from 1st January 1900 to 6th June 2079, with accuracy of 1 minute, for example: "2007-10-14 19:35".

The next two are new in SQL Server 2008. "date" stores only the date component without the time component, ranging from 1st January 0001 to 31st December 9999, with accuracy of 1 day, for example: "2007-10-14". "time" stores only the time component (without the date component), ranging from 00:00:00.000000 to 23:59:59.9999999 with accuracy of 100 nanosecond (7 decimal places), for example: "19:35:09.3579284".

The last two are also new in SQL Server 2008. "datetime2" stores both date and time components, ranging from 1st January 0001 to 31st December 9999, with accuracy of 100 ns, for example: "2007-10-14 19:35:09.3579284. "datimeoffset" stores both date and time components and the time zone offset, ranging from 1st January 0001 to 31st December 9999, with accuracy of 100 ns, for example: "2007-10-14 19:35:09.3579284 +02:15. The time zone offset ranges from -14:00 to +14:00.

In SQL Server 2000 and 2005 we have a data type called "timestamp", which is a misleading name because it does not contain the time or date component. It contains an automatically generated unique binary number, used as a mechanism of version stamping the rows in a table. The synonym for "timestamp" is "rowversion", which is a better name in my opinion. In SQL Server 2008, we still have "rowversion" data type, but the term "timestamp" is deprecated, meaning that it still exists, but it will not be included in the future version of SQL Server. An example of rowversion usage is: "create table table1 (col1 int, col2 rowversion)". If the row is updated, the value of rowversion column changes. We can use rowversion to determine whether any value in the row has changed.

In SQL Server 2008 we have 6 date and time functions that return the current system timestamp:

1. GETDATE
2. CURRENT_DATETIME
3. GETUTCDATE
4. SYSDATETIME
5. SYSDATETIMEOFFSET
6. SYSUTCDATETIME

The first 3 are the same as in SQL Server 2000 and 2005. GETDATE function returns the current system timestamp with accuracy of .333 second. CURRENT_TIMESTAMP function is equivalent to GETDATE function. GETUTCDATE function returns the current UTC date and time (Coordinated Universal Time or Greenwich Mean Time) with accuracy of .333 second, derived from the current local time and the time zone setting of the server where SQL Server is running. All three functions above returns datetime data type.

The last 3 are new in SQL Server 2008. SYSDATETIME function returns the current system timestamp without the time zone, with accuracy of 10 milliseconds. SYSDATETIMEOFFSET function is the same is SYSDATETIME, but with the time zone. SYSUTCDATETIME returns the UTC date and time with accuracy of 10 milliseconds, derived from the current local time and the time zone setting of the server where SQL Server is running. Both SYSDATETIME and SYSUTCDATETIME return datetime2 data type, where as SYSDATETIMEOFFSET returns datetimeoffset data type.

So as a highlight, SQL Server 2008 has date-only and time-only data types, date and time with time zone offset and a new data type with a wider date range and higher accuracy called datetime2. This article is based on SQL Server 2008 July CTP. While on the one hand it is good to know in advance about SQL Server 2008 (so we can plan for our future applications), on the other hand we also need to understand that it is still in development and it is subject to change.

Vincent Rainardi
17th September 2007