SmallDateTime vs DateTime

Size

  • A SmallDateTime data type is a 4 bytes date and time.
  • A DateTime data type is a 8 bytes date and time.

Date range

  • SmallDateTime: From January 1, 1900 00:01 (AM), until June 6, 2079 23:59 (PM)
  • DateTime: From January 1, 1753, 00:00:00:03 (AM) until December 31, 9999 23:59:59:99:99 (PM)
  • If you need a date range between the 18th century and the 20th century or need dates greater than 2079 then use DateTime.

Precision

  • SmallDateTime: A 1 minute precision. It does not keep track of the seconds.
  • DateTime: A 3.33 milliseconds precision. The values are rounded to increments of .000, .003, or .007 seconds.
  1. DECLARE @vardate01 DATETIME;
  2. DECLARE @vardate02 SMALLDATETIME;
  3. SET @vardate01 = GETDATE();
  4. SET @vardate02 = GETDATE();
  5.  
  6. SELECT @vardate01 AS 'Full Date & Time',
  7.        @vardate02 AS 'Small Date & Time';
  8. go
  9.  
  10. SELECT CAST('2007-06-11 14:45:12.789' AS DATETIME) AS '8 bytes Date & Time',
  11.        CAST('2007-06-11 14:45:12.789' AS SMALLDATETIME)  AS '4 bytes Date & Time';
  12. GO
[Get Code]
Full Date & Time        Small Date & Time
----------------------- -----------------------
2007-06-11 14:32:56.297 2007-06-11 14:33:00

(1 row(s) affected)

8 bytes Date & Time     4 bytes Date & Time
----------------------- -----------------------
2007-06-11 14:45:12.790 2007-06-11 14:45:00

(1 row(s) affected)

If you can, use a SmallDateTime data type instead of DateTime data type:

  1. It is smaller, therefore less disk space and IO.
  2. It is faster to process.