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 DATETIMEDECLARE @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 |