datetime vs smalldatetime

Question

If I don't care about less than seconds, what should be the recommended type to store my datetime values in sql-server 2005, is it datetime or smalldatetime or else?

Answer 1

If you don't care about seconds, dates earlier than 1900 or later than 2079, smalldatetime will be fine :) Otherwise you're better off with datetime.

http://msdn.microsoft.com/en-us/library/aa258277%28SQL.80%29.aspx

Answer 2

smalldatetime has a resolution down to one minute, so if that resoulution is OK for you, smalldatetime is 50% smaller (4 bytes vs. 8 bytes) so is preferred.

See http://www.karaszi.com/SQLServer/info_datetime.asp for a good overview.

Answer 3

If you're unsure, use datetime - anything else is premature optimization. You might change your mind about needing seconds in the future.