Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Convert Oracle String Functions to SQL Server String Functions
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

In SQL Server, each column, local variable, expression and parameter has a related data type.  A data type is an attribute that specifies the type of data that the object can hold, such as date and time data.  Two date/time-related data types are the DATETIME and DATETIME2 data types.

The following article shows the similarities and differences between the DATETIME data type and the DATETIME2 data type introduced in SQL Server 2008.

Similarities

  • Both data types define a date that is combined with a time of day that is based on a 24-hour clock.
  • Both data types does not include the time zone offset.
  • Default value for both data types is January 1, 1900 00:00:00.
  • Both data types are not aware of the daylight saving time.

Differences

DATETIME

DATETIME2

Date range is between January 1, 1753 through December 31, 9999

Date range is between January 1, 0001AD through December 31, 9999 AD

Time range is between 00:00:00 through 23:59:59.997

Time range is between 00:00:00 through 23:59:59.9999999

Syntax is simply DATETIME

DECLARE @CurrentDate DATETIME

Syntax is DATETIME2 [ (Fractional Seconds Precision) ], with a default value of 7 for the fractional seconds precision.

DECLARE @CurrentDateTime DATETIME2(7)

Accuracy is up to 0.00333 second

Accuracy is up to 100 nanoseconds

Storage size is 8 bytes

Storage size is between 6 to 8 bytes

Character length is from 19 positions minimum (YYYY-MM-DD hh:mm:ss) to 23 maximum (YYYY-MM-DD hh:mm:ss.000)

Character length is from 19 positions (YYYY-MM-DD hh:mm:ss) minimum to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)

No precision or scale can be specified

Precision or scale is from 0 to 7 digits, with an accuracy of 100ns and a default precision value of 7 digits.

Available from SQL Server 2000 and onwards

Available only from SQL Server 2008 and onwards

Back to Tip of the Day List Next Tip