“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.

Comments (2)

  1. wma says:

    I’m sure I’m not the first person to suggest this but wouldn’t it make more sense to name datetime2 as bigdatetime.

  2. A few days ago I described the new date and time data types coming in SQL Server 2008. To get the complete