Most efficient way to store only the date?

Question

DateTime is accurate to three-hundredths of a second. SmallDateTime is accurate to one minute.

What if you only need accuracy to a day? What is the best practice here? I realise that a DateTime is 8 bytes, and SmallDateTime is only 4; but I am just wondering is there a better or more suitable way to store only a date value than creating a column of SmallDateTime and saving SmallDateTime.Date in it ?

Every little helps! :)

You have seen the Date (only) type in SQL 2008? 3 Bytes http://msdn.microsoft.com/en-us/library/bb630352(v=sql.100).aspx — StuartLC, Apr 24 '12 at 15:37
What environment are you developing in which is going to be noticeably affected by a few bytes? — Lloyd Powell, Apr 24 '12 at 15:37
Ask yourself if you really need to do this optimization? [YAGNI](http://en.wikipedia.org/wiki/You_ain't_gonna_need_it) — Ian, Apr 24 '12 at 15:38
@KirkWoll 2008 ThePower I never said it would be drastically affected. I like to use best practice irrespective of this. YAGNI-Ian See above! :) — Simon Kiely, Apr 24 '12 at 15:40
@Ian you don't know the scenario, so YAGNI doesn't apply. perhaps this is an indexed column with 200 million rows so you would like to make sure your index stays in memory — Darren Kopp, Apr 24 '12 at 15:43

score 5 · Accepted Answer · answered Apr 24 '12 at 15:42

It depends on what version of SQL Server you are running.

2008+: Date
2008-: SmallDateTime

Although, if you look at the documentation for smalldatetime, they use a two-byte integer for date and two-byte integer for time, so you could likely replicate their behavior and just store a two-byte integer (smallint) and then use DateAdd to add the number of days since 1900/1/1 if size is really that important in this scenario.

score 2 · Answer 2 · edited Apr 24 '12 at 15:45

2

Use the 3 byte Date. See Date data type

edited Apr 24 '12 at 15:45

Jamiec

133,658
13
134
193

answered Apr 24 '12 at 15:42

htoverkill

89
7

Most efficient way to store only the date?

2 Answers2