You have a
lot of options when storing date and time information in SQL Server. There are
a host of data types for such values. The most common is DATETIME. It takes up
8 bytes on disk. The year portion can vary between 1753 and 9999. It is
accurate to 1/300th of a second.
If you want
more control over the fractional number of seconds stored, you can use the
DATETIME2 data type. You define how accurate it is. You pass this type a
parameter at time of creation to specify exactly how many fractional digits are
stored. Based on that number, this data type can use of 6 to 8 bytes on space
on disk.
There is
also a DATE data type. It takes up 3 bytes on disk. It only stores calendar
information. It does not store any time values. The year can range from 0001 through
9999. Similarly, there is a TIME data type. It is the opposite of DATE. It does
not store any calendar information. It only has time info.
If storage space is at a premium, you can opt to
use the SMALLDATETIME data type. It takes up 4 bytes on disk. It can have a
year between 1900 and 2079. The time portion is accurate to within one minute.
Seconds are not stored in this data type. So the seconds are always assumed to
be zero.
