RSS Feed

Difference between DATETIME2 / DATETIME

Difference between DATETIME2 / DATETIME


DATETIME2 DATETIME
DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31".
January 1,1 CE through December 31, 9999 CE.
DATETIME type only supports year 1753 to 9999.
Rounding of datetime2 Fractional Second Precision:
Declare @d2 datetime2 = '3/31/2016 23:59:59.999'
Select @d2 as 'datetime2'

Result:
datetime2
2016-03-31 23:59:59.9990000
Rounding of datetime Fractional Second Precision:
Declare @d1 datetime = '3/31/2016 23:59:59.999'
Select @d1 as 'datetime'

Result:
datetime
2016-04-01 00:00:00.000
Example of Datetime2 with 0 precision:
DECLARE @dt1 datetime2(0) = getdate()
SELECT @dt1 as 'datetime2', DATALENGTH(@dt1) as bytes_used

Result:
datetime2 bytes_used
2016-03-17 22:23:39 6


Example of Datetime2 with 7 precision:
DECLARE @dt2 datetime2(7) = getdate()
SELECT @dt2 as 'datetime2', DATALENGTH(@dt2) as bytes_used

Result:
datetime2 bytes_used
2016-03-17 22:23:38.6570000 8


Storage size is 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.
DECLARE @dt3 datetime = getdate()
SELECT @dt3 as 'datetime', DATALENGTH(@dt3) as bytes_used

Result:
datetime bytes_used
2016-03-17 22:23:38.657 8


Storage size is 8 bytes.