MySQL Type for Storing a Year: Smallint or Varchar or Date?

Question

I will be storing a year in a MySQL table: Is it better to store this as a smallint or varchar? I figure that since it's not a full date, that the date format shouldn't be an answer but I'll include that as well.

Smallint? varchar(4)? date? something else?

Examples:

• 2008

• 1992

• 2053

Answer 1

I would use the YEAR(4) column type... but only if the years expected are within the range 1901 and 2155... otherwise, see Gambrinus's answer.

Answer 2

I'd go for small-int - as far as I know - varchar would take more space as well as date. second option would be the date.

Answer 3

My own experience is with Oracle, which does not have a YEAR data type, but I have always tried to avoid using numeric data types for elements just because they are comprised only of digits. (So this includes phone numbers, social security numbers, zip codes as well, as additional examples).

My own rule of thumb is to consider what the data is used for. If you will perform mathematical operations on it then store it as a number. If you will perform string functions (eg. "Take the last four characters of the SSN" or "Display the phone number as (XXX) XXX-XXXX") then it's a string.