Reading the details of the Date and Datetime datatype at: http://www.mysql.com/doc/en/DATETIME.html
I see that 1000-01-01 is the earliest date that MySQL "supports" in this datatype. We have an application that needs to store a lot of historical data, the majority of which will fit nicely above the year 1000 lower limit. However, although there are relatively few pieces of history we need to store before the year 1000 they are equally important. We are likely to have dates such as 0005-04-10 or even BC dates (-)3000-00-00. Our application will be producing a "timeline" of events, so we will need to sort all of the dates that are entered in chronological order. To work around the limitations of the Datetime field, I propose to implement the following structure for dates. Field: my_year (Integer, Signed) Field: my_month (Integer, unsigned) Field: my_day (Integer, unsigned) To store BC dates I intend to use a negative integer for the year. Ideally I am aiming to avoid having two storage formats for dates, so implementing date datatype fields for years above 1000 and another system for those below is not really an option. As this is the first time I will have used the above method of storing dates, can anyone see any potential "gotchas"/pitfalls? Alternatively, is there a better method which I could use? TIA for your time and help. Regards, Roland
