New datetime datatypes in SQL Server 2008

From the beginning, Microsoft SQL Server DBAs have longed
for two different datatypes, where you could store time and date separately.
The long wait is over. In all of the older versions, we had only one data
type that could store Datetime datatype, and it stored both the value of the
date and the value of the time.

Finally, in Microsoft SQL Server 2008, Microsoft is
introducing a set of new datatypes for storing date, time and both date and time
together. The new data types store more data as well, which means you can store
dates anywhere from 01-01-01 to 9999-12-31 and also store time up to the
fraction of 9999999.

This article illustrates the usage and functionality of different
datatypes in Microsoft SQL Server 2008, namely date, time, datetime, datetime2
and datetimeoffset datatypes.

Note: This article is written based on the Microsoft SQL Server 2008 – Nov
CTP

TIME

The datatype TIME is primarily used for storing the time
of a day. This includes Hours, minutes, Seconds etc. It is based on a 24-hour
clock. The datatype TIME can store seconds up to the fraction of 9999999.

Let us declare a variable with the datatype TIME and store
some data.


declare @mytime TIME
set @mytime = GETDATE()
select MyTime = @mytime

The result is shown below. [Refer Fig 1.0]

MyTime
01:48:18.4870000



Fig 1.0

When you declare a variable with datatype TIME with no
precision, SQL Server assumes a 7 digit precision as shown above.

The time
range is from 00:00:00 through 23:59:59.9999999.

We could limit the precision of the datatype as shown
below.


declare @mytime TIME(3)
set @mytime = GETDATE()
select MyTime = @mytime

The result is shown below. [Refer Fig 1.2]

Result


MyTime
01:51:25.580



Fig 1.1

DATE

The datatype DATE is used for storing the date only. This
includes Month, Day and year.

The value of date can be anywhere from 01-01-01 to 9999-12-31.

Let’s declare a variable with the datatype DATE and store
some data.


declare @mydate DATE
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = ’01/01/1997′
Select Mydate = @MyDate
set @mydate = ’01/31/9999′
Select Mydate = @MyDate
set @mydate = ’01/01/0001′
Select Mydate = @MyDate

The range for date is
from 0001-01-01 through 9999-12-31

The result is shown below. [Refer Fig 1.2]

Result


MyDate
2007-12-18

MyDate
1997-01-01

MyDate
9999-01-31

MyDate
0001-01-01



Fig 1.2

DATETIME2

The data type DATETIME2 is the combination of the datatype
DATE and TIME. DATETIME2 is used to store both a date value ranging from 01-01-01 to 9999-12-31 as well as a time value up to the
fraction of 9999999.

Let’s declare a variable with the datatype DATETIME2 and
store some data as shown below.


declare @mydate DATETIME2
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = convert(datetime2,’01/01/1997 16:14:00.1234567′)
Select Mydate = @MyDate
set @mydate = convert(datetime2,’01/01/0001 16:14:00.1234567′)
Select Mydate = @MyDate
The range for DATETIME2 is from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

The result is shown below. [Refer Fig 1.3]

Result


MyDate
2007-12-18 02:11:45.3130000

MyDate
1997-01-01 16:14:00.1234567

MyDate
0001-01-01 16:14:00.1234567



Fig 1.3

We could limit the precision as shown below.


Declare @mydate DATETIME2(4)
set @mydate = GETDATE()
Select Mydate = @MyDate
set @mydate = convert(datetime2(4),’01/01/1997 16:14:00.1234567′)
Select Mydate = @MyDate

The result is shown below. [Refer Fig 1.2]

Result


MyDate
2007-12-18 02:14:54.8130

MyDate
1997-01-01 16:14:00.1235

SMALLDATETIME and DATETIME

Microsoft SQL Server 2008 continues to support existing
data types such as datetime and smalldatetime.

The range for the
datatype smalldatetime is from 1900-01-01 through 2079-06-06. Execute the following query
as shown.


Declare @Mydate datetime
Set @MyDate = getdate()
Select MyDate = @MyDate

The result is shown
below. [Refer Fig 1.4]

Result


MyDate
2007-12-18 02:31:35.347



Fig 1.4

The range for the
datatype datetime is from 1753-01-01 through 9999-12-31.

Execute the following
query as shown.


Declare @Mydate smalldatetime
Set @MyDate = getdate()
Select MyDate = @MyDate

The result is shown
below. [Refer Fig 1.5]

Result


MyDate
2007-12-18 02:34:00.000



Fig 1.5

DATETIMEOFFSET

Microsoft SQL Server 2008 introduces a new datetime
datatype called datetimeoffset. Datetimeoffset actually defines the date with
the combination of the time of a day that is timezone aware. In addition, the
clock is a 24-hour cycle.

The timezone offset range is from -14:00 through +14:00

Execute the query below to get the timeoffset.


Select
CAST(‘2007-05-08 12:35:29.1234567 +12:15’ AS datetimeoffset(7)) AS ‘datetimeoffset’

The result is shown below. [Refer Fig 1.6]

Result


datetimeoffset
2007-05-08 12:35:29.1234567 +12:15



Fig 1.6

You can use the convert and cast functions to convert to
and from different datetime datatypes.

Example:


Declare @mydate DATETIME2(4)
set @mydate = GETDATE()
select @mydate as OriginalDate
select CONVERT (smalldatetime,@mydate ) as Smalldate
select CONVERT (time,@mydate ) as Justtime

Result


OriginalDate
2007-12-19 02:15:09.3130

Smalldate
2007-12-19 02:15:00.000

Justtime
02:15:09.3130000

Note: This article is written based on the Microsoft SQL Server 2008 – Nov
CTP.

Conclusion

This article has illustrated the usage and function of the
various datatypes related to Date and time, namely date, time, datetime,
datetime2 and datetimeoffset.

»


See All Articles by Columnist
MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles