SQL Server Helper - Tip of the Day

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

Back to Tip of the Day List

Next Tip