SQL DataType - How to store a year?

database types sqldatatypes
peter picture peter · Mar 30, 2009 · Viewed 99.2k times · Source

I need to insert a year(eg:1988 ,1990 etc) in a database. When I used Date or Datetime data type, it is showing errors. Which datatype should I use.

Answer

KM. picture KM. · Mar 30, 2009

regular 4 byte INT is way to big, is a waste of space!

You don't say what database you're using, so I can't recommend a specific datatype. Everyone is saying "use integer", but most databases store integers at 4 bytes, which is way more than you need. You should use a two byte integer (smallint on SQL Server), which will conserve space better.

Related questions

Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?

In multiple courses, books, and jobs, I have seen text fields defined as VARCHAR(255) as kind of the default for "shortish" text. Is there any good reason that a length of 255 is chosen so often, other than being a nice …

Read More
Common MySQL fields and their appropriate data types

I am setting up a very small MySQL database that stores, first name, last name, email and phone number and am struggling to find the 'perfect' datatype for each field. I know there is no such thing as a perfect …

Read More
Best practices for storing postal addresses in a database (RDBMS)?

Are there any good references for best practices for storing postal addresses in an RDBMS? It seems there are lots of tradeoffs that can be made and lots of pros and cons to each to be evaluated -- surely this …

Read More