Home / Software / Tips and Tricks / New Date Data Types in Microsoft SQL Server 2008.

New Date Data Types in Microsoft SQL Server 2008.

Updated:  10/29/2010 09:10 AM
Author:  Shiju Mathews

Status:    Resolved.


In August 2008 Microsoft released the latest version of the database server software, SQL Server 2008. SQL Server 2008 includes a number of new features not found in SQL Server 2005, including: a terser T-SQL syntax; the new MERGE statement; new data types and functions; enhanced encryption and XML support.

In previous versions, SQL Server had only two date-related data types: datetime and smalldatetime, both of which allow date and time values (the difference being that datetime allows for a larger range of possible dates and affords more precision on the time than smalldatetime, but at the cost of larger storage space). SQL Server 2008 introduces four new date data types: time, date, datetime2, and datetimeoffset.

  • SQL Server 2008's Date Data Types
    Previous versions of SQL Server offered two date data types: datetime and smalldatetime. These data types were sufficient for most applications, but could be cumbersome in certain cases. For instance, both data types have a date and time portion, which is great if that's what you want, but cumberson if all you need to store is just the date or just the time. Similarly, the date ranges imposed by these two data types - 1753-01-01 to 9999-12-31 for datetime and 1900-01-01 to 2079-06-06 for smalldatetime - are insufficient for a small percentage of applications.

    Data type Format Range Accuracy Storage size (bytes)
    time hh:mm:ss[.nnnnnnn] 00:00:00.0000000 through 23:59:59.9999999 100 nanoseconds 3 to 5
    date YYYY-MM-DD 0001-01-01 through 9999-12-31 1 day 3
    smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 through 2079-06-06 1 minute 4
    datetime YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 through 9999-12-31 0.00333 second 8
    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
    datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 in UTC 100 nanoseconds 8 to 10
  •