MySQL:Represent years greater than 9999

According to the MySQL reference manual, the year type is restricted to at most 9999 if I understand it correctly. (See http://dev.mysql.com/doc/refman/5.7/en/year.html).

So how then do you store large future or past dates? For example 6000BC or what got me asking, 21974AD? I have tried to find examples of using larger dates but haven't been able to and wondered if it's at all possible to do so.

(Small background to this, was testing some Java date-time validations, and found trying to insert record for the year 21974 into the database, causes an exception, due to the previously mentioned limitation)

If you need to work only with years, you can represent them by means of any integer (that is: from smallint to bigint (tinyint is too small) or numeric/decimal) type of a size big enough for your needs.

If you really need date calculations involving years > 9999, you're out luck. MySQL documentation about dates clearly states that


The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

If your needs for date ranges are really critical, and you have the possibility of choosing your database, PostgreSQL allows much larger ranges (4713 BC to 5874897 AD for dates, 4713 BC to 294276 AD for timestamps).

Other databases (Firebird, Oracle and MS SQL Server for instance) have date possible values similar to those of mySQL.

StackExchange Database Administrators Q#159330, answer score: 5