New Date and Time Data Types

SQL Server 2008 introduces enhancements to the existing date and time data types. The most important changes are the addition of separate DATE and TIME data types. The DATETIME2 data type adds more precision to the existing DATETIME data type, and DATETIMEOFFSET provides support for time zones. Here is a list of the new data types:

New data types:

• DATE (0001-01-01 through 9999-12-31)
• TIME (00:00:00.0000000 through 23:59:59.9999999)
• DATETIME2 (fraction 0 through 7)
• DATETIMEOFFSET (time zone awareness)

There are also a few new functions to support those new data types. Here is a list of the added date and time functions:

New functions:

• SYSDATETIME
• SYSDATETIMEOFFSET
• SYSUTCDATETIME
• SWITCHOFFSET
• TODATETIMEOFFSET

Follows a brief example to illustrate the use of the new date and time data types as well as the new functions.

-- The range for the DATE datatype is from 0001-01-01 through 9999-12-31
DECLARE @date DATE;
 
SET @date = CURRENT_TIMESTAMP;
 
PRINT @date;
 
-- The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999
DECLARE @time TIME;
 
SET @time = CURRENT_TIMESTAMP;
 
PRINT @time;
 
-- Fraction 0 through 7
DECLARE @datetime2 DATETIME2(7);
 
SET @datetime2 = CURRENT_TIMESTAMP;
 
PRINT @datetime2;
 
SET @datetime2 = SYSDATETIME();
 
PRINT @datetime2;
 
-- Timezone offset range: -14:00 through +14:00
-- Date range: 0001-01-01 through 9999-12-31
-- Time range: 00:00:00 through 23:59:59.9999999
DECLARE @datetimeoffset DATETIMEOFFSET(7);
 
SET @datetimeoffset = CURRENT_TIMESTAMP;
 
PRINT @datetimeoffset;
 
SET @datetimeoffset = SYSDATETIMEOFFSET();
 
PRINT @datetimeoffset;
 
-- Functions
SELECT SYSDATETIME(),
       SYSDATETIMEOFFSET(),
       SYSUTCDATETIME(),
       CURRENT_TIMESTAMP,
       GETDATE(),
       GETUTCDATE();
 
-- New ISO week option for week number calculation       
SELECT DATEPART(ISO_WEEK, CURRENT_TIMESTAMP);    
 
-- Switch between time zones
SELECT SYSDATETIMEOFFSET(),
       SWITCHOFFSET(SYSDATETIMEOFFSET(), '+06:00'); --Paris
 
-- Set date and time offset based on DATETIME value
SELECT CURRENT_TIMESTAMP,
       TODATETIMEOFFSET(CURRENT_TIMESTAMP, '-01:00');
 
-- Convert DATETIME to DATE and TIME       
SELECT CONVERT(DATE, CURRENT_TIMESTAMP),
       CONVERT(TIME, CURRENT_TIMESTAMP);

Additional resources:

Using Date and Time Data
http://msdn.microsoft.com/en-us/library/ms180878(SQL.100).aspx

Date and Time Data Types and Functions
http://msdn.microsoft.com/en-us/library/ms186724(SQL.100).aspx