It could be I already have an answer to this, but I just wanted to put it by the community here before implementing it, to check this is the right way to go.
The problem is having tables with (historic) dates, but I don't always have data for an exact date.
In some rows I know the full date: year, month and day. But in others I just know the year and month, or just the year and sometimes no date info at all.
I was thinking of the best way to handle this. For no data a
NULL value will do, but with partial data I have in the past tried invalid dates like
1885-06-00 if I know the month but not the day, but some apps and systems cough at such invalid data.
Mysql has a
YEAR type, but not
DAY types, so I'm not sure if using a 3 column system would be a good idea, as I would lose the built in date functionality.
Searching around I found this on SO:-
Which gave me the idea I was thinking of going with.
So I may store a date as
1885-06-01 instead of
1885-06-00, then have an extra column for specificity
(Y|M|D) which in this case would be
M, as I have accuracy to the month here.
So I would still be storing valid dates, I would then just need to do a little PHP function to deal with the specificity.
Any thought on this? Improvements, better ideas?