Date and Time Data Types in SQL Server

With the introduction of SQL Server 2008, Microsoft answered the pleas of many database professionals by introducing revamped date and time data types. These new data types allow designers to easily work with time zones, dates without times (and vice versa) and dates in ancient history and far into the future. In this article, we take a look at the date and time data types available to users of SQL Server 2008. Data types in the date and time category include:

• datetime variables store 8-byte time and date values ranging from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds.
• datetime2 variables use between 6-8 bytes to store dates and times between January 1, 0001 and December 31, 9999 with an accuracy of 100 nanoseconds.
• smalldatetime variables store 4-byte time and date values ranging from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.
• date variables use 3 bytes to store a date only (with no time information) in the range January 1, 0001 through December 31, 9999.
• time variables use between 3-5 bytes to store a time only (with no date information) to an accuracy of 100 nanoseconds.
• datetimeoffset variables store the date and time using between 8-10 bytes. The values stored are the same as those stored by the datetime2 datatype with the addition of a time zone offset.
• timestamp variables are automatically populated by SQL Server with the time that a row is created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable.

If the date and time data types don’t suit your needs, read more about other SQL Server data types.