Appendix E. Date/time data types in SQL Server 2008
SQL Server 2008 introduces a number of new data types. In chapter 9 we covered one of these, FILESTREAM. In addition to a couple of geospatial types (GEOGRAPHY and GEOMETRY), the major new types are HIERARCHYID, used for storing data representing positions in a hierarchical structure (for example, an organizational chart), and four date-related types: DATE, TIME, DATETIME2, and DATETIMEOFFSET. Let’s spend a little time exploring the new date data types, beginning with DATE.
In SQL Server 2005 and earlier, there was no way of storing a date without a time component (unless stored as a string). Further, the earliest date that could be stored was 1 Jan 1753. Fortunately, SQL Server 2008 introduces the DATE type, and as the name suggests, it stores only the date without a time component.
Consuming just 3 bytes (compared to 8 bytes for DATETIME), DATE types are stored in the format YYYY-MM-DD and permit dates from 0001-01-01 through to 9999-12-32.
One of the limitations of the old DATETIME data type (which is still available) is the precision of the time component. Accurate to .333 second, the DATETIME type lacked granularity for certain applications, an issue addressed by the TIME type.