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?
Asked
Active
Viewed 7,455 times
3 Answers
10
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.
Aaron Bertrand
- 272,866
- 37
- 466
- 490
Justin Grant
- 44,807
- 15
- 124
- 208
7
If you're unsure, use datetime - anything else is premature optimization. You might change your mind about needing seconds in the future.
Mark Ransom
- 299,747
- 42
- 398
- 622
-
if you change your mind about that, than it's no problem to change the type at that specific moment. Or am I missing something? :) – Dirk Boer Jun 14 '13 at 11:16
-
2@DirkBoer, once you've stored data in the database it's going to be a pain to change it - and the missing information can't be added later. – Mark Ransom Jun 14 '13 at 13:29
4
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
MartW
- 12,348
- 3
- 44
- 68