Database Reference
In-Depth Information
The actual value that ends up in the database will be
12/01/2006 18:00:00.000
In contrast,
smalldatetime
would store the same value as
12/01/2006 18:00
Additionally, datetime stores any date between January 1, 1753, and
December 31, 9999, whereas smalldatetime stores only values ranging
from January 1, 1900, to June 6, 2079. It may seem strange that these date
ranges where chosen; the reason lies in the storage requirements at the
disk level and the way the actual data is manipulated internally in SQL
Server.
As we mentioned, SQL Server 2008 provides four new date and time
data types: date, time, datetime2, and datetimeoffset. These new data types
store date and time data in more flexible ways than their predecessors. The
date
and
time
data types are the most straightforward; they store only the
date portion or only the time portion of a given value. The
datetime2
data
type, which is not cleverly named, is just like datetime except that you can
specify a variable length for the precision of fractional seconds from 0 to 7.
The
datetimeoffset
data type is similar to datetime except that in addition
to the date and time, you specify an offset value. Your offset is not tied to any
particular time zone, such as Greenwich Mean; instead you have to know the
time zone you are using as the base from which to compare your values.
We have covered a lot of ground here, and again we refer you to
Chapter 3 for a longer discussion of the reasons these data types store data
the way they do.
It can be tempting, when you're designing a logical model, to quickly
gloss over the chosen data types for each attribute. This practice can cause
a number of design problems later in development. For one thing, most
data modeling software can generate a physical design based on the logical
model, so choosing inappropriate data types in the logical model can lead
to confusion in the physical design, particularly when multiple developers
are involved. Be sure to refer frequently to the business requirements to
ensure that you are defining attributes based on the data that will be
stored. This practice will also help when you're discussing the model with
nontechnical stakeholders.
Search WWH ::
Custom Search