Storing partial dates

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I am creating my first mySQL database to store genealogy information and
would like fields for date of birth and date of death.

Unfortunately some older records are quite vague with only the year being
available in some cases. I am concerned that if I store only the year or
month & year where available then when the information is retrieved then
the missing information will be defaults which are incorrect.

For example if I store the date of birth as "March 1900" how can I avoid
this being retrieved as "1st March 1900"

Would it be best practice to store the day month & year in separate fields ?


Re: Storing partial dates

Murphy wrote:
Quoted text here. Click to load it
 > Would it be best practice to store the day month & year in separate
fields ?

NULL is sometimes used to indicate missing or unspecified information.
NULL exists in SQL because "no value" is different from zero or an empty
string, or some special date like 1-1-1900.  Those values can have
significance, depending on the application.  Like yours -- someone could
have a birthdate of 1-1-1900.

But NULL is a state of a field, not part of a field.  So yes, I think
you should store the day, month, and year as separate fields in your
case, so that you can set any of the three to a NULL state.

Then you would need to write logic in your application to do an
appropriate thing with a NULL in any of these fields.  For instance,
display only the year if the other two are NULL.  Or giving the user a
means to to fill in the additional values if their genealogy research
reveals them later.

Bill K.

Site Timeline