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:
Example of Datetime2 with 7 precision: DECLARE @dt2 datetime2(7) = getdate() SELECT @dt2 as 'datetime2', DATALENGTH(@dt2) as bytes_used Result:
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:
Storage size is 8 bytes. |