Are you a startup?
Get BizSpark cloud access
Get up to $3,700 of cloud benefits
Don’t have MSDN?
Here’s cloud access
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:
Storage size (bytes)
User-defined fractional second precision
Time zone offset
00:00:00.0000000 through 23:59:59.9999999
3 to 5
00001-01-01 through 9999-12-31
1900-01-01 through 2079-06-06
1753-01-01 through 9999-12-31
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999
6 to 8
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)
8 to 10
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.
I'm sure I'm not the first person to suggest this but wouldn't it make more sense to name datetime2 as bigdatetime.
A few days ago I described the new date and time data types coming in SQL Server 2008. To get the complete