Difference between DateTime and DateTime2 in SQL Server

Suggested Videos
Part 126 - DATEFROMPARTS function in SQL Server
Part 127 - Difference between DateTime and SmallDateTime in SQL Server
Part 128 - DateTime2FromParts function in SQL Server 2012



In this video we will discuss the difference between DateTime and DateTime2 in SQL Server



Differences between DateTime and DateTime2 

Attribute	DateTime	DateTime2
Date Range	January 1, 1753, through December 31, 9999	January 1, 0001, through December 31, 9999
Time Range	00:00:00 through 23:59:59.997	00:00:00 through 23:59:59.9999999
Accuracy	3.33 Milli-seconds	100 nanoseconds
Size	8 Bytes	6 to 8 Bytes (Depends on the precision)
Default Value	1900-01-01 00:00:00	1900-01-01 00:00:00

DATETIME2 has a bigger date range than DATETIME. Also, DATETIME2 is more accurate than DATETIME. So I would recommend using DATETIME2 over DATETIME when possible. I think the only reason for using DATETIME over DATETIME2 is for backward compatibility.

DateTime2 Syntax : DATETIME2 [ (fractional seconds precision) ]

With DateTime2 

• Optional fractional seconds precision can be specified
• The precision scale is from 0 to 7 digits
• The default precision is 7 digits
• For precision 1 and 2, storage size is 6 bytes
• For precision 3 and 4, storage size is 7 bytes
• For precision 5, 6 and 7, storage size is 8 bytes

The following script creates a table variable with 7 DATETIME2 columns with different precision start from 1 through 7

DECLARE @TempTable TABLE

(

    DateTime2Precision1 DATETIME2(1),

    DateTime2Precision2 DATETIME2(2),

    DateTime2Precision3 DATETIME2(3),

    DateTime2Precision4 DATETIME2(4),

    DateTime2Precision5 DATETIME2(5),

    DateTime2Precision6 DATETIME2(6),

    DateTime2Precision7 DATETIME2(7)

)

Insert DateTime value into each column

INSERT INTO @TempTable VALUES

(  

    '2015-10-20 15:09:12.1234567',

    '2015-10-20 15:09:12.1234567',

    '2015-10-20 15:09:12.1234567',

    '2015-10-20 15:09:12.1234567',

    '2015-10-20 15:09:12.1234567',

    '2015-10-20 15:09:12.1234567',

    '2015-10-20 15:09:12.1234567'

)

The following query retrieves the prcision, the datetime value, and the storage size.

SELECT 'Precision - 1' AS [Precision],

         DateTime2Precision1 AS DateValue,

         DATALENGTH(DateTime2Precision1) AS StorageSize

FROM @TempTable

UNION ALL

SELECT 'Precision - 2',

         DateTime2Precision2,

         DATALENGTH(DateTime2Precision2) AS StorageSize

FROM @TempTable

UNION ALL

SELECT 'Precision - 3',

         DateTime2Precision3,

         DATALENGTH(DateTime2Precision3)

FROM @TempTable

UNION ALL

SELECT 'Precision - 4',

         DateTime2Precision4,

         DATALENGTH(DateTime2Precision4)

FROM @TempTable

UNION ALL

SELECT 'Precision - 5',

         DateTime2Precision5,

         DATALENGTH(DateTime2Precision5)

FROM @TempTable

UNION ALL

SELECT 'Precision - 6',

         DateTime2Precision6,

         DATALENGTH(DateTime2Precision6)

FROM @TempTable

UNION ALL

SELECT 'Precision - 7',

         DateTime2Precision7,

         DATALENGTH(DateTime2Precision7) AS StorageSize

FROM @TempTable

Notice as the precision increases the storage size also increases