New Date and Time Data Types in SQL Server 2008

By: Editor
March 06, 2010

Introduction

SQL Server 2008 introduces four new DATE and TIME data types namely TIME, DATE, DATETIME2 and DATETIMEOFFSET. In this article we will take a look into each of these new data types in detail. 

DATE Data Type

In the previous versions of SQL Server there was no date specific inbuilt data type available to store only a date value. As a result you would end up using either SMALLDATETIME or DATETIME data type which will also add the current date along with the current time value. Finally to get the date value you need to use either the CONVERT function or use TSQL to get the relevant date value which is stored in the database. It has become much easier in SQL Server 2008 with the introduction of DATE data type to get the desired results.

DECLARE @DateTime AS DATETIME
SET           @DateTime = GETDATE()

SELECT 
	CAST(@DateTime AS DATE) AS 'DATE'
GO
DATE
------------
2010-03-06

TIME Data Type

In the previous versions of SQL Server there was no time specific inbuilt data type available to store only a time value. As a result you would end up using either SMALLDATETIME or DATETIME data type which will also add the current time along with the current data value. Finally to get the time value you need to use either the CONVERT function or use TSQL to get the relevant time value from the data which is stored in the database. It has become much easier in SQL Server 2008 with the introduction of TIME data type to get the desired results.

DECLARE 	@DateTime AS DATETIME
SET           @DateTime = GETDATE()

SELECT 
    CAST(@DateTime AS TIME(7)) AS 'TIME'
GO

TIME
------------------
22:00:00.4500000


DATETIME2 Data Type

The new DATETIME2 date type can be considered as an enhancement to the existing DATETIME data type. The DATETIME2 data type supports a larger date range and a larger fractional second precision when compared to existing DATETIME date type. The date range which DATETIME2 data type can support is between 0001-01-01 through 9999-12-31. And the time range which DATETIME2 data type can support is between 00:00:00 through 23:59:59.9999999 i.e., with an accuracy of 100 nanoseconds.

DECLARE 	@DateTime AS DATETIME
SET           @DateTime = GETDATE()

SELECT 
    CAST(@DateTime AS DATETIME2(7)) AS 'DATETIME2'
GO

DATETIME2
------------------------------
2010-03-06 22:10:10.0000000

DATETIMEOFFSET Data Type

The new DATETIMEOFFSET data type is very useful especially when you have to deal with time zone information. The DATETIMEOFFSET defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

DECLARE @DateTime AS DATETIME
SET           @DateTime = GETDATE()

SELECT 
	CAST(@DateTime AS DATETIMEOFFSET(7)) AS 'DATETIMEOFFSET'
GO
DATETIMEOFFSET
-------------------------------------
2010-03-06 22:10:10.0000000 +00:00

Execute the below mentioned TSQL code to clearly understand the differences between the outputs of each of the DATA and TIME data types that are available in SQL Server 2008 and SQL Server 2008 R2.

USE TEMPDB
GO

DECLARE	@DateTime AS DATETIME
SET           @DateTime = GETDATE()

SELECT 
	 CAST(@DateTime AS SMALLDATETIME) AS 'SMALLDATETIME' 
	,CAST(@DateTime AS DATETIME) AS 'DATETIME' 
	,CAST(@DateTime AS DATE) AS 'DATE' 
	,CAST(@DateTime AS TIME(7)) AS 'TIME'
	,CAST(@DateTime AS DATETIME2(7)) AS 'DATETIME2'
	,CAST(@DateTime AS DATETIMEOFFSET(7)) AS 'DATETIMEOFFSET'
GO

SQL Server 2008 also introduces five new Data and Time functions namely SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, SWITCHOFFSET and TODATETIMEOFFSET. To know more about each one of them you can check the following article named Date and Time Functions in SQL Server 2008.

Conclusion

In this article you have seen in detail how to use the new Data and Time Data Types which are available in SQL Server 2008 and SQL Server 2008 R2.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."