Welcome to MSDN Blogs Sign in | Join | Help
SQL Server 2008 - Enhancements in Date and Time Data Types

I am finding SQL Server 2008 very exciting. Every day it's a new learning for me and my eyes glitter seeing the sweet surprises that SQL Server 2008 is bringing in for efficient data management. I like SQL Server because I realize that data is like the life blood for any business, any forecasting any strategic decisions to be taken depends on the facts and figures. When so many things depend on data then efficient presentation of data with precision is most important. Precision in Date and Time is important thing that we have to maintain. When the business is growing globally, it needs to maintain the Time Zones, precision of time in nanoseconds etc. So to cater to such requirements SQL Server 2008 - (Katmai) has introduced new date/time data types.

Please look at the table (7x7 Matrix) to take a quick overview of the Data Types  :

Data type
 Format Range Accuracy Storage size (bytes) User-defined fractional second precision Time zone offset
time hh:mm:ss[.nnnnnnn] 00:00:00.0000000 through 23:59:59.9999999 100 nanoseconds 3 to 5 Yes No
date YYYY-MM-DD 00001-01-01 through 9999-12-31 1 day 3 No No
smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 through 2079-06-06 1 minute 4 No No
datetime YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 through 9999-12-31 0.333 second 8 No No
datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 nanoseconds 6 to 8 Yes No
datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 00001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) 100 nanoseconds 8 to 10 Yes Yes

 

1) TIME : This data type is useful to define the time of the day, this data type is not Time Zone aware and it is based on a 24 hour clock. This data type is not aware of Day Light Saving. This data type is capable of handling high precision time in a small storage space.

The storage space taken by the TIME data type is as follows:

Specified Scale
 Result (precision, scale) Column length (bytes) Fractional seconds precision
time (16,7) 5 7
time(0) (8,0) 3 0-2
time(1) (10,1) 3 0-2
time(2) (11,2) 3 0-2
time(3) (12,3) 4 39145
time(4) (13,4) 4 39145
time(5) (14,5) 5 39209
time(6) (15,6) 5 39209
time(7) (16,7) 5 39209

 

2) DATE : This data type is useful to store the dates without the time part, we can store dates starting from 00001-01-01 through 9999-12-31 i.e. January 1, 1 A.D. through December 31, 9999 A.D. It supports the Gregorian Calendar and uses 3 bytes to store the date.

Code Sample

Use AdventureWorksLT

Go

Create Schema Trade

Go

CREATE TABLE Trade.StockTran

(

TransID        BIGINT IDENTITY(1,1) PRIMARY KEY ,

StockTicker    CHAR(4),

Qty            INT,

TransDate      DATE,

TransTime      Time(7)

)

Go

INSERT INTO Trade.StockTran (StockTICKER,Qty,TransDate,TransTime)

Values('RAVI',20,sysdatetime(),sysdatetime())

Go

INSERT INTO Trade.StockTran (StockTICKER,Qty,TransDate,TransTime)

Values('RAVI',-5,sysdatetime(),sysdatetime())

Go

Select * from Trade.StockTran

Output

TransID              StockTicker Qty         TransDate  TransTime
-------------------- ----------- ----------- ---------- ----------------
1                    RAVI        20          2007-08-28 23:43:04.0972273
2                    RAVI        -5          2007-08-28 23:43:04.1128530

 

3) SMALLDATETIME : This is a data type that has the accuracy to 1 minute and useful for storing dates and time when the precision doesn't matter too much for example. The order booking date and time of a user. The range supported by this type of data type is 1990-01-01 through 2079-06-06 or January 1, 1900, through June 6, 2079 and time range between 00:00:00 through 23:59:59. The data type takes 4 fixed bytes to store the data.

 

4) DATETIME : This is a well known data type by most of us the date range supported is 01-01-1753 through 9999-12-31 or January 1, 1753, through December 31, 9999 and time range supported is 00:00:00 through 23:59:59.997. It takes 8 bytes to store the date/time data.

 

5) DATETIME2 : This is a new data type introduced in SQL Server 2008 and this date/time data type is introduced to store the high precision date and time data. The data type can be defined for variable lengths depending on the requirement. Please refer the following table for more information on the data type. This data type also follows the Gregorian Calendar and is not Day Light Saving Aware. The Time Zone can't be specified in this data type. This is still useful because it gives you a complete flexibility to store the date time data as per your requirement.

Specified scale
 Result (precision, scale) Column length (bytes) Fractional seconds precision
datetime2 (27,7) 8 7
datetime2 (0) (19,0) 6 0-2
datetime2 (1) (21,1) 6 0-2
datetime2 (2) (22,2) 6 0-2
datetime2 (3) (23,3) 7 39145
datetime2 (4) (24,4) 7 39145
datetime2 (5) (25,5) 8 39209
datetime2 (6) (26,6) 8 39209
datetime2 (7) (27,7) 8 39209

 

 

CODE SAMPLE

 

Create Table Trade.DateTest

(

DateID INT IDENTITY(1,1) PRIMARY KEY,

Dt1 DATETIME2,

Dt2 DATETIME2(0),

Dt3 DATETIME2(1),

Dt4 DATETIME2(2),

Dt5 DATETIME2(3),

Dt6 DATETIME2(4),

Dt7 DATETIME2(5),

Dt8 DATETIME2(6),

Dt9 DATETIME2(7)

)

Go

--Insert Today's Date

DECLARE @Now as DATETIME2(7)

SELECT @Now = SYSDATETIME()

INSERT INTO Trade.DateTest

(Dt1,Dt2,Dt3,Dt4,Dt5,Dt6,Dt7,Dt8,Dt9)

VALUES (@Now,@Now,@Now,@Now,@Now,@Now,@Now,@Now,@Now)

Go

--Insert Starting Range

DECLARE @Now as DATETIME2(7)

SELECT @Now = '0001-01-01 00:00:00'

INSERT INTO Trade.DateTest

(Dt1,Dt2,Dt3,Dt4,Dt5,Dt6,Dt7,Dt8,Dt9)

VALUES (@Now,@Now,@Now,@Now,@Now,@Now,@Now,@Now,@Now)

Go

--Insert Ending Range

DECLARE @Now as DATETIME2(7)

SELECT @Now = '9999-12-31 23:59:59.9999999'

 

INSERT INTO Trade.DateTest

(Dt1,Dt2,Dt3,Dt4,Dt5,Dt6,Dt7,Dt8,Dt9)

VALUES (@Now,@Now,@Now,@Now,@Now,@Now,@Now,@Now,@Now)

GO

SELECT * FROM Trade.DateTest

 

OUTPUT

NOTE: The Matrix is transposed to fit in the blog space.

DateID 1 2 3
Dt1 2007-08-29 21:04:17.4445484 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999
Dt2 2007-08-29 21:04:17 0001-01-01 00:00:00 9999-12-31 23:59:59
Dt3 2007-08-29 21:04:17.4 0001-01-01 00:00:00.0 9999-12-31 23:59:59.9
Dt4 2007-08-29 21:04:17.44 0001-01-01 00:00:00.00 9999-12-31 23:59:59.99
Dt5 2007-08-29 21:04:17.445 0001-01-01 00:00:00.000 9999-12-31 23:59:59.999
Dt6 2007-08-29 21:04:17.4445 0001-01-01 00:00:00.0000 9999-12-31 23:59:59.9999
Dt7 2007-08-29 21:04:17.44455 0001-01-01 00:00:00.00000 9999-12-31 23:59:59.99999
Dt8 2007-08-29 21:04:17.444548 0001-01-01 00:00:00.000000 9999-12-31 23:59:59.999999
Dt9 2007-08-29 21:04:17.4445484 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999

 

6) DATETIMEOFFSET : This is the new data type that is included in SQL Server 2008 and this data type is the most advanced in the league. We can store high precision date/ time with the Date Time Offset. We can't store the Time Zone like Eastern Time, Central Time etc. in the data type but can store the offset -5:00 for EST and -6:00 CST and so on. The data type is not Day light saving aware.

The date range is between 0001-01-01 and 9999-12-31 or January 1,1 A.D. through December 31, 9999 A.D. and the Time Range is between 00:00:00 and 23:59:59.9999999. The offset range is between -14:00 through +14:00. The precision of the data type can be set manually and it follows the Gregorian Calendar. For more details look at the following table.

Specified scale
 Result (precision, scale) Column length (bytes) Fractional seconds precision
datetimeoffset (34,7) 10 7
datetimeoffset(0) (26,0) 8 0-2
datetimeoffset(1) (28,1) 8 0-2
datetimeoffset(2) (29,2) 8 0-2
datetimeoffset(3) (30,3) 9 39145
datetimeoffset(4) (31,4) 9 39145
datetimeoffset(5) (32,5) 10 39209
datetimeoffset(6) (33,6) 10 39209
datetimeoffset(7) (34,7) 10 39209

 

CODE SAMPLE

 

Create Schema CorpIT  

Go

Create Table CorpIT.LoginCredentials

(

UserID INT IDENTITY(1,1) PRIMARY KEY,

domainname varchar(50) NOT NULL,

loginname varchar(50) NOT NULL,

creationDate DATETIMEOFFSET(7) NOT NULL

)

Go 

--INSERT A DATE VALUE WITH THE US CENTRAL TIME ZONE OFFSET

DECLARE @CreateDt CHAR(50)

SELECT @CreateDt = '2007-05-08 12:35:29.1234567 -6:00'

INSERT CorpIT.LoginCredentials

(domainname, loginname,creationdate)

VALUES('NorthAmerica','SCOTT', @CreateDt)

Go

--INSERT A DATE VALUE WITH THE INDIAN TIME ZONE

DECLARE @CreateDt CHAR(50)

SELECT @CreateDt = '2007-05-08 12:35:29.1234567 +5:30'

INSERT CorpIT.LoginCredentials

(domainname, loginname,creationdate)

VALUES('Asia','RAVI', @CreateDt)

GO 

--INSERT A DATE VALUE WITH THE AUSTRALIAN TIME ZONE

DECLARE @CreateDt CHAR(50)

SELECT @CreateDt = '2007-05-08 12:35:29.1234567 +10:00'

INSERT CorpIT.LoginCredentials

(domainname, loginname,creationdate)

VALUES('Australia','Nigel', @CreateDt) 

 

 

OUTPUT

UserID domainname loginname creationdate
1 NorthAmerica SCOTT 2007-05-08 12:35:29.1234567 -06:00
2 Asia RAVI 2007-05-08 12:35:29.1234567 +05:30
3 Australia Nigel 2007-05-08 12:35:29.1234567 +10:00
Posted: Tuesday, August 28, 2007 6:33 PM by Ravi

Comments

dariusj18 said:

I am slightly confused on one point and I hope that maybe you can answer.  I love the idea of DateTimeOffset but it seems like a waste to make such a great improvement without including DST or some mechanism to allow true multilocation UTC datetime storage.

So I guess my question is why create this enhancement without making it truley feature complete?  I understand that there would be additional processing required but wouldn't automated UTC date storage be the main reason anyone would use this datatype instead of just storing the datetime and the time zone offset in seperate fields.

I guess while I am asking this question I may as well include another (non related) question.  I love the idea of .NET CLR integration.  Especially the ability to create your own custom aggregate functions.  But it makes me wonder why a string concat aggregate wasn't added to SQL server along with the all of the other improvments (Love the WITH CTE, Row_Number and OVER).  Sometimes as a web developer it just isn't practical to add a CLR UDF.  Often us smaller developers don't have the advantage of being able to add assemblies to the SQL server.

# August 31, 2007 1:26 AM

Ravi said:

Dear Reader,

I thank you for reading my blog. I am answering your questions below and let me know if they are satisfactory or not :-)

This is a very good question why the DST is not available. DST is quite a complex and large feature – especially given the clear difficulties handling GeoPolitical DST issues (i.e. new rules, rule changes and etc). The .NET Orcas new DateTimeOffset provides much powerful and flexible Time Zone & DST support.

I have passed your feedback to the concerned people and we will seriously consider the possibility of adding full DST support in next major release.

I would like to have more details what are you looking in the String functionality.

Thank you very much using SQL Server and providing your valuable feedbacks.

Regards,

Ravi

# September 1, 2007 2:33 PM

Azra [Florent Santin] said:

Carl Perry - Senior Program Manager Lead Microsoft Apr�E8;s mon aventure hors de mon bocal sur Windows

# November 6, 2007 10:36 AM
Anonymous comments are disabled
Page view tracker