mysql year lower than 1900

Question

i created a table "composers" VALUES (name VARCHAR(30), birth YEAR(4), death YEAR(4), city VARCHAR(30), country VARCHAR(30)). then i INSERT INTO composers VALUES ('BACH Johann Sebastian',1685,1750,'Eisenach','Germany');. it returns 2 errors. the years are not between 1901 and 2155.

is there any way of changing this default so i can have an actual year for those columns? cant seem to find anything on the web.

i resorted to just use INTEGER(4) for those columns but would like to use YEAR(4) if it's possible.

Answer 1

It seems quite clear that this is the only way this type works in the docs.

The YEAR(4) type only uses 1 byte, and is limited to a range of 256 years. If your use case falls completely within a different 256 year space, you could certainly store them off by a certain amount, and adjust the yourself before writing and after reading, say by -300.

Alternatively, use a SMALLINT or UNSIGNED SMALLINT instead of a full INT to use 2 bytes. Not quite the 1 byte that a 'YEAR(4)' uses, but less than the 4 bytes for an INT

Answer 2

The YEAR datatype only supports that range. If you want to use other values, use a standard INT.

The only advantage to using the YEAR type is semi-automatic conversion from 2-digit years to 4-digit equivalents, but that utility is dubious at best.