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