CODEWITHSUNDEEP
performancesql-server-2008datetimesmalldatetime
Simon Kiely
Simon Kiely

Reputation: 6050

Most efficient way to store only the date?

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! :)

Upvotes: 0

Views: 227

Answers (2)

htoverkill
htoverkill

Reputation: 89

Use the 3 byte Date. See Date data type

Upvotes: 2

Darren Kopp
Darren Kopp

Reputation: 77627

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.

Upvotes: 5

Related Questions