6.2.2.4 The YEAR Type

The YEAR type is a 1-byte type used for representing years.

MySQL retrieves and displays YEAR values in YYYY format. The range is 1901 to 2155.

You can specify YEAR values in a variety of formats:

• As a four-digit string in the range '1901' to '2155'.
• As a four-digit number in the range 1901 to 2155.
• As a two-digit string in the range '00' to '99'. Values in the ranges '00' to '69' and '70' to '99' are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
• As a two-digit number in the range 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999. Note that the range for two-digit numbers is slightly different from the range for two-digit strings, because you cannot specify zero directly as a number and have it be interpreted as 2000. You must specify it as a string '0' or '00' or it will be interpreted as 0000.
• As the result of a function that returns a value that is acceptable in a YEAR context, such as NOW().

Illegal YEAR values are converted to 0000.

User Comments

Since there doesn't seem to be a date type for
representing historic dates (eg. 2300 BC, or 129
CE), how would I achieve this using the existing date
type? Or is there something I'm missing?

When trying to insert ´historical´ values try using
a smallint (5) signed. this will give you values in the
range of -9999 to 99999

For my needs (timelines, etc, etc), having a separate year field would be impractical. So I have a BIGINT field, as my date field. Then use the programing language to sort out the number into a friendlier format. If the number is negative then the application (in this case a php website) sets the age to BCE. The raw format I put the dates into would be YEAR MONTH DAY. Example:
March 12, 2500 BCE == -25000312
March 12, 2003 == 20030312

With the BIGINT I can get dates from Jan. 1 922337203685477 BCE to Dec. 31 922337203685477 CE

Of course, there may be better ways...like perhaps MySQL being so kind as to add support for BCE/CE dates with years longer than 4 digits.

Add your own comment.