Data Management

New Date Datatypes in SQL Server 2008

SQL Server 2008 provides new data-types for storing wider ranges of dates with more precision. Heres can you can use these new data types to your advantage in SQL Server 2008.

As a database architect, one of the features I am most excited about in SQL Server 2008 is the new date related data types. These data types allow for the storage of time-only data, date-only data, a larger range of date values, more time precision, and the ability to store time-zone specific dates. Today I'll take a look at these great new data types and how they can be used to expand your date storage possibilities.

DATE

The new DATE data-type defines a date field without any associated time. In previous versions of SQL Server, storing time-only data was accomplished in one of two ways. First, you could define either a DATETIME or SMALLDATETIME data type and assign the value as a VARCHAR value. Then you would need to handle the time related data in your queries. The other way is to store the date value in a VARCHAR field in the database. Either way, handling date-only data was at best cumbersome. With the new DATE data-type, it intrinsically stores only date values. In the following example I define a DATE data-type and a DATETIME data type and assign today's date to the variables. As you can see from SELECTing the values from the variables, the DATE variable holds only the date portion whereas the DATETIME field also stores the associated time from the GETDATE() function.

DECLARE @TodaysDate DATE = GETDATE()
DECLARE @TodaysDateTime DATETIME = GETDATE()
SELECT @TodaysDate, @TodaysDateTime

Storing DATE specific data will also make queries easier to write. Instead of using one of the following statements to pull data for a DATETIME column;

SELECT * FROM SalesHistory
WHERE SaleDate >= ‘4/11/2008' AND SaleDate < ‘4/12/2008'

you can use an equality operator (=) to search only for the specific date, assuming the SaleDateOnly field is defined as a DATE data-type.

SELECT * FROM SalesHistory
WHERE SaleDateOnly = ‘4/11/2008'

DATETIME2

This data type is very simiilar to the datetime data type present in previous versions of SQL Server. The difference is the time precision and the range of dates that DATETIME2 can store. The new DATETIME2 data type can hold a range of dates from 0001-01-01 through 9999-12-31 while DATETIME holds the date range from 1753-01-01 to 9999-12-31. Another benefit of this data type is the precision in which it records time. DATETIME2 is accurate to within 100 nanoseconds whereas DATETIME is accurate to within 3/1000 of a second. The following script compares the values that the DATETIME and the DATETIME2 data-types can hold.

DECLARE @DateTime2 DATETIME2 = GETDATE()
DECLARE @DateTime DATETIME = GETDATE()
SELECT @DateTime, @DateTime2

Notice the additional precision stored in the DATETIME2 variable. This additional data can be of major significance for those applications where time related information is mission-critical.

TIME

The TIME data-type stores only the time portion of a date. This is significant in that the time data can be stored seperately and not associated with a specific date. The TIME data-type, like the DATETIME2 data-type is accurate up to 100 nanoseconds. The following script compares the time specific information returned by the TIME data type and that which is returned from a DATETIME data type.

DECLARE @Time TIME = GETDATE()
DECLARE @DateTime DATETIME = GETDATE()
SELECT @Time, @DateTime

DATETIMEOFFSET

The new DATETIMEOFFSET data type combines the range and precision of the DATETIME2 data-type along with time-zone awareness based on a 24 hour clock. The time-zone awareness is accomplished by adding or subtracting an hours + minutes value to the date data. In the following example, I define a temporary table that holds two fields. The first field holds the date that is local to my area. The second field, BeijingTime, is an offset date that will hold the time in Beijing, China; which happens to be 12 hours ahead of the time in Louisville, KY.

CREATE TABLE #OffSets
(
	LouisvilleTime DATETIME2 DEFAULT(GETDATE()),
	BeijingTime AS CONVERT(VARCHAR(50), LouisvilleTime, 121) + '+12:00'
)
INSERT INTO #OffSets(LouisvilleTime)
SELECT GETDATE()
SELECT * FROM #OffSets

As you can see from the results from the table, the BeijingTime field holds the date from the LouisvilleTime field along with a 12 hour offset to indicate Beijing time. This data-type greatly enhances the ability to create time-zone aware applications.

TIME to try the new data-types

The new date related data-types included in SQL Server 2008 greatly expand the database developers ability to not only store a wider range of dates and precision for those dates, but also the ability to zone time-zone specific data which can lead to more robust and accurate reporting of time-related data around the world.

 

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

Editor's Picks

Free Newsletters, In your Inbox