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

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://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes for a good overview.

Answer 2

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(SQL.80).aspx

Answer 3

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