PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 53

Date and Time Data Types
When you need to store a date or time value, SQL Server provides you
with six data types. Knowing which type to use is important, because each
date and time data type provides a slightly different level of accuracy, and
that can make a huge difference when you're calculating exact times, as
well as durations. Let's look at each in turn.
Datetime and Smalldatetime
The datetime and smalldatetime data types can store date and time data in
a variety of formats; the difference is the range of values that each can
store. Datetime can hold values from January 1, 1753, through December
31, 9999, and can be accurate to 3.33 milliseconds. In contrast, smalldate-
time can store dates only from January 01, 1900, through June 6, 2079, and
is accurate only to 1 minute. For storage, datetime requires 8 bytes, and
smalldatetime needs only 4 bytes.
Date and Time
New in SQL Server 2008 are data types that split out the date portion and
the time portion of a traditional date and time data type. Literally, as the
names imply, these two data types account for either the date portion
(month, day, and year), or the time portion (hours, minutes, seconds, and
nanoseconds). Thus, if needed, you can store only one portion or the other
in a column.
The range of valid values for the date data type are the same as for the
datetime data type, meaning that date can hold values from January 1,
1753, through December 31, 9999. From a storage standpoint, date re-
quires only 3 bytes of space, with a character length of 10.
The time data type holds values 00:00:00.0000000 through
23:59:59.9999999 and can hold from 8 characters (hh:mm:ss) to 16 char-
acters (hh:mm:ss: nnnnnnn) , where n represents fractional seconds. For ex-
ample, 13:45:25.5 literally means that it is 1:45:25 and one-half second
p.m. You can specify the scale of the time data type from 0 to 7 to desig-
nate how many digits you can use for fractional seconds. At its maximum,
the time data type requires 5 bytes of storage.
Datetime2
Another new data type in SQL Server 2008 is the datetime2 data type. This
is very similar to the original datetime data type, except that datetime2 in-
corporates the precision and scale options of the time data type. You can