Mysql what field type to use for dates with a mix of full and part dates

Question

I have a table that has three different date columns, so I set each column as type 'date'.

However whenever im importing dates it seems to change them, and I have found it is because mysql does not allow null days and months.

My dates range from

1909-00-00 1963-09-00 1907-11-30

so sometimes we dont know the month, sometimes the day, and sometimes only the year was recorded.

Surely the 'proper' way to do this is to use some kind of date field but I have tried a variety of different layouts when importing the data and non works :( dd-mm-yyyy mm-dd-yyyy yyyy/dd/mm

Any suggestions?

Answer 1

I would suggest using a varchar then converting it when ever you need it to be a date. Here is a site with some date functions that would be useful.

Answer 2

There is no data structure that would act as a date but would allow 00 for any of the components. If you really insisted on emulating this, you could split the data into 3 columns - year, month, day and then write a complicated trigger to validate each update/insert query.

I wouldn't dare though. However, you asked, and I tried to answer. ;]