Wednesday, February 25, 2015

Difference between DateTime2, DateTime and SmallDateTime

Here are the main differences you should know about these three date types:

  Range of Dates

Accuracy

Size

usage Applies to
smalldatetime

January 1, 1900 - June 6, 2079

one Minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

4 bytes

DECLARE @myDate SmallDatetime

SQL 2000 >+
Datetime

January 1, 1753 - December 31, 9999

three-hundredths of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).Values are rounded to increments of .000, .003, or .007 seconds

8 bytes

DECLARE @myDate Datetime

SQL 2000 >+
Datetime2

January 1, 0001 - December 31, 9999

100ns

can take 6-8 Bytes; by default it will take 7 bytes. 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes

DECLARE @myDate   Datetime2(7)

>=SQL 2008

 

 

Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.

0 comments:

Post a Comment