Difference between DateTime2, DateTime and SmallDateTime

Post a Comment

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

  Range of Dates



usage Applies to

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 >+

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 >+

January 1, 0001 - December 31, 9999


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.

Related Posts

There is no other posts in this category.

Post a Comment

Subscribe Our Newsletter