Skip to content

Similarities and Differences Between DATETIME and DATETIME2 Data Types

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.
vailable from SQL Server 2000 and onwards Available only from SQL Server 2008 and onwards