“Get a Date…”

SQL Server 2008 introduces some long awaited new data types for representing dates and times. These include separate types for date and time, a high precision datetime type (datetime2), and a type that includes timezone information (datetimeoffset).

The following table (from the books online) describes all the date and time 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

The new types behave as you would expect them to when converting between types, including implicitly converting from datetime times to date or time types by truncating. There are also new functions included to facilitate creating time zone aware dates from standard dates (TODATETIMEOFFSET) and to switch the timezone information of a datetimeoffset value (SWITCHOFFSET).

One thing to be aware of is that date + time arithmetic is not available (at least as of the time of this writing), and so building a datetime value from separate date and time values will require a little extra work. Here is one example:

DECLARE @date1 date = '2007-05-01';
DECLARE @time1 time = '13:37:08.050';
DECLARE @dt datetime2;

SET @dt = dateadd(ms, datediff(ms, 0, @time1), cast(@date1 as datetime2));

select @dt;

-----------------------
2007-05-01 13:37:08.050

Tomorrow we will explore how to use these new data types from .net code.