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.
Post a Comment
Post a Comment