New datetime data types in SQL Server 2008 offer flexibility

The fifth and final tip in this series on SQL Server datetime values outlines four new data types supported by T-SQL in SQL Server 2008. SQL Server expert Robert Sheldon walks you through how to use DATETIME2, DATETIMEOFFSET and DATE and TIME data types -- flexible features that separate date and time, unlike the limited DATETIME data type in SQL Server 2005.

In this tip series I've covered many aspects of datetime values in SQL Server. The examples I have shown you were written for SQL Server 2005, though much of what I discussed in those articles could also apply to SQL Server 2008. In this tip, I take the discussion of datetime values one step further and focus specifically on the new datetime data types available in SQL Server 2008. They include the DATE, TIME, DATETIME2 and DATETIMEOFFSET...

types as they're supported in Transact-SQL.

To demonstrate these types, I use the following code to create the Sales.OrderDates table in the AdventureWorks2008 sample database and to insert test data into the table:

USE AdventureWorks2008
GO
IF EXISTS (SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'Sales'
AND table_name = 'OrderDates')
DROP TABLE Sales.OrderDates
GO
CREATE TABLE Sales.OrderDates
(
OrderID INT NOT NULL,
Date_Type DATE NULL,
Time_Type TIME(7) NULL,
DateTime2_Type DATETIME2(7) NULL,
DateTimeOffset_Type DATETIMEOFFSET(7) NULL
)
GO
INSERT INTO Sales.OrderDates
VALUES(1001, '2008-09-22', '18:27:10.1234567',
'2008-09-22 18:27:10.1234567', '2008-09-22 18:27:10.1234567 -07:00')

If you want to try out the examples in this article, you can create the table in the AdventureWorks2008 database or in a different database. If you decide to use the AdventureWorks2008 database, the necessary files are available from CodePlex.

The DATE data type

Prior to SQL Server 2008, the primary datetime data types were DATETIME and SMALLDATETIME. In each case, the value stored in the type contained both the date and time, and there was no way to store one portion without the other. However, SQL Server 2008 changes all that with the DATE and TIME data types.

As the name suggests, the DATE data type stores a date value. The value can range from January 1, 1 A.D. through December 31, 9999 A.D. The value includes the year, month and day. For example, the following SELECT statement retrieves data from the OrderID and Date_Type columns:

SELECT OrderID, Date_Type FROM Sales.OrderDates

The Date_Type column is configured with the DATE data type, so the value returned contains only a date, as in the following results:

OrderID Date_Type
1001 2008-09-22

In this case, the DATE value is returned by year, then month, then day. However, the format in which the date is returned is determined by the current language setting in SQL Server.

If you want to return a datetime value as a DATE value, you can explicitly convert the value, as in the following SELECT statement:

SELECT OrderID, CAST(DateTimeOffset_Type AS DATE) AS ConvertedType
FROM Sales.OrderDates

In this case, the statement retrieves the value from the DateTimeOffset_Type column, which is configured with the DATETIMEOFFSET data type (explained later in the article). This statement returns the same results as those returned by the previous statement, even though the retrieved value contains both date and time data. In other words, the time portion of the value is ignored.

When inserting data into a DATE column, you can specify a date value or a datetime value, as shown in the following example:

INSERT INTO Sales.OrderDates (OrderID, Date_Type)
VALUES (1002, '2008-09-20');
INSERT INTO Sales.OrderDates (OrderID, Date_Type)
VALUES (1003, '2008-09-21 18:27:10.1234567');

When you specify a datetime value, SQL Server automatically converts that value into the DATE data type, which means that only the date portion is stored.

The TIME data type

The TIME data type stores only the time value. The value itself supports a fractional precision of 7. This means that the fractional part of the seconds can support up to seven decimal places, compared to a DATETIME value, which has a fractional precision of 3. The precision of the TIME data type supports a range of 00:00:00.0000000 through 23:59:59.9999999.

When you specify the TIME data type in a Transact-SQL statement, you can specify the precision of the stored values by including the appropriate number within parentheses. For example, to specify a precision of 7, you would specify TIME(7). For a precision of 5, you would specify TIME(5), and so on. If you do not specify the precision, 7 is assumed.

The TIME data type returns data in the form of hour, minute, second and fractional second. For instance, the following SELECT statement retrieves data from the Time_Type column:

SELECT OrderID, Time_Type FROM Sales.OrderDates

The Time_Type column is configured with the TIME(7) data type, so it returns data in the following form:

OrderID Time_Type
1001 18:27:10.1234567

Notice that the column returns only the time and that the value has a fractional precision of 7.

As you saw with the DATE data type, you can also return a TIME value from a datetime column. For example, the following statement retrieves data from the DateTimeOffset_Type column and converts the value to TIME(7):

SELECT OrderID, CAST(DateTimeOffset_Type AS TIME(7)) AS ConvertedType
FROM Sales.OrderDates

The SELECT statement returns the same results as the previous SELECT statement.

You can, however, specify a different precision when converting the data, as shown in the following example:

SELECT OrderID, CAST(DateTimeOffset_Type AS TIME(5)) AS ConvertedType
FROM Sales.OrderDates

The fractional part of the seconds now includes only five decimal places:

OrderID ConvertedType
1001 18:27:10.12346

Notice that the fractional seconds have been rounded up. The original fractional seconds were .1234567. However, if you specify a precision of 5, SQL Server will round up the "67" fractional part, returning .12346 in the results, rather than .12345.

As with a DATE column, when inserting data into a TIME column, you can specify a time value or a datetime value as shown in the following INSERT statements:

INSERT INTO Sales.OrderDates (OrderID, Time_Type)
VALUES (1004, '18:27:10.1234567');
INSERT INTO Sales.OrderDates (OrderID, Time_Type)
VALUES (1005, '2008-09-20 18:27:10.1234567');

In both cases, only the time is inserted into the Time_Type column.

The DATETIME2 data type

The DATETIME2 data type is similar to the DATETIME data type. The difference is that DATETIME2 supports a wider range of dates (same as DATE) and a greater fractional precision (same as TIME). And like the TIME data type, you can specify that precision.

In other words, the DATETIME2 data type basically combines a DATE value and a TIME value. For example, the following SELECT statement returns data from the DateTime2_Type column, which is configured as a DATETIME2 column:

SELECT OrderID, DateTime2_Type FROM Sales.OrderDates

The statement returns the following results:

OrderID DateTime2_Type
1001 2008-09-22 18:27:10.1234567

As you can see, the DATETIME2 value first provides the date, then the time, with a fractional precision of 7. Except for the seven digits of the fractional seconds, this value looks just like a DATETIME value –or a DATE valued followed by a TIME value.

You can also convert other datetime values to a DATETIME2 value, as in the following statement:

SELECT OrderID, CAST(DateTimeOffset_Type AS DATETIME2(7)) AS ConvertedType
FROM Sales.OrderDates

Notice that I'm converting a DATETIMEOFFSET value to DATETIME2 and that I'm specifying a precision of 7. This statement will return the same results as the previous statement.

But the results are quite different if you convert other types of values. For instance, the following statement retrieves data from the Date_Type column, which is configured with the DATE data type and converts the value to DATETIME2(7):

SELECT OrderID, CAST(Date_Type AS DATETIME2(7)) AS ConvertedType
FROM Sales.OrderDates

When SQL Server converts the value, it adds a default time to the new value, as shown in the following results:

OrderID ConvertedType
1001 2008-09-22 00:00:00.0000000

Notice that the time is all zeroes (with a precision of 7), which is the first value of the 24-hour clock. Whenever the time needs to be assumed (as in this case), all zeroes are used, which means that the default time value is 00:00:00.0000000.

The default value for the date is quite different. In the following statement, I convert the value from the Time_Type column, which is configured with the TIME data type, to a DATETIME2(7) value:

SELECT OrderID, CAST(Time_Type AS DATETIME2(7)) AS ConvertedType
FROM Sales.OrderDates

Because no date is included in a TIME value, SQL Server assumes a date, as shown in the following results:

OrderID ConvertedType
1001 1900-01-01 18:27:10.1234567

As you can see, the date is January 1, 1900. This is the default date when no date is provided.

The DATETIMEOFFSET data type

The DATETIMEOFFSET data type is nearly identical to the DATETIME2 data type, except that a DATETIMEOFFSET value includes

More tips on using T-SQL features in SQL Server:

 one important addition: a time-zone offset value. The offset value represents the number of hours and minutes before or after Coordinated Universal Time (UTC). A positive number indicates the amount of time to add to the UTC to determine the local time. A negative number indicates the amount of time to subtract from the UTC to determine the local time.

Let's take a look at an example to better understand how this works. The following SELECT statement retrieves data from the DateTimeOffset_Type column, which is configured with the DATETIMEOFFSET data type:

SELECT OrderID, DateTimeOffset_Type FROM Sales.OrderDates

In the following results, you see that the DATETIMEOFFSET value includes the time-zone offset, which is -07:00:

OrderID DateTimeOffset_Type
1001 2008-09-22 18:27:10.1234567 -07:00

The returned value indicates that you must subtract seven hours from the datetime value in order to obtain the local time.

When you try to convert another type of datetime value to a DATETIMEOFFSET value, the time-zone offset is set to the default of +00:00. For example, the following SELECT statement converts a DATETIME2 value to DATETIMEOFFSET:

SELECT OrderID, CAST(DateTime2_Type AS DATETIMEOFFSET(7)) AS ConvertedType
FROM Sales.OrderDates

Here are the results:

OrderID ConvertedType
1001 2008-09-22 18:27:10.1234567 +00:00

Now the time-zone offset is +00:00, which means, in this case, that the local time is the same as the UTC.

If you were to try to convert a DATE value to DATETIMEOFFSET, the time part of the value will be all zeroes. For instance, the following statement converts the Date_Type column to DATETIMEOFFSET(7):

SELECT OrderID, CAST(Date_Type AS DATETIMEOFFSET(7)) AS ConvertedType
FROM Sales.OrderDates

The statement returns the following results:

OrderID ConvertedType
1001 2008-09-22 00:00:00.0000000 +00:00

Notice that the time and the time-zone offset are set to zero.

However, if you convert a TIME value, the date part of the returned value is set to January 1, 1900, the default value, as demonstrated by the following SELECT statement:

SELECT OrderID, CAST(Time_Type AS DATETIMEOFFSET(7)) AS ConvertedType
FROM Sales.OrderDates

The results here show the converted data:

OrderID ConvertedType
1001 1900-01-01 18:27:10.1234567 +00:00

The time is set to the default value, and the time-zone offset is set to +00:00. Only the time value itself reflects the original value.

As you can see, the DATETIMEOFFSET data type, along with the DATE, TIME and DATETIME2 data types, greatly extend the datetime capabilities of SQL Server. Now you can work with dates and times as separate values, work with a wider range of date values and define greater precision in your time values. For more details about each of these date types, see Microsoft SQL Server 2008 Books Online.


Tip Series: Working with date/time data types in SQL Server

 Part 1: Basics for working with DATETIME and SMALLDATETIME

 Part 2: Data conversions from date/time values to character types
 Part 3: Using datetime functions GETDATE, DATENAME and DATEPART
 Part 4: Using DATEADD and DATEDIFF to calculate datetime values
 Part 5: New datetime data types in SQL Server 2008

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

MEMBER FEEDBACK TO THIS TIP

Do you have a comment on this tip? Let us know.

I appreciate the articles, tips, and knowledge Mr. Sheldon shares in his writings. They're thorough, easy to understand, and complete. I also imagine they take some time to produce. I personally appreciate that he is willing to take the time to write them.
—Lee C.


This was first published in October 2008

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close