Storing partial dates

[Murphy]

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 ?
--
Thanks

Murph

[Bill Karwin]

Murphy wrote:

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 ?

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.

Regards,
Bill K.

[Ray Gurganus]

"Murphy" <[e-mail]> wrote in message
news:k4******** **********@news-server.bigpond. net.au...

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

I did a genealogy database myself, and for this I created two date fields
for each case - one as a text field that stores what is typed in, and a
second that converts this to the actual closest date for sorting and
comparison purposes. So "March 1900" is coverted over to 1/1/1900 in the
actual date field, but the application displays the text field.

[Murphy]

Ray Gurganus wrote:

"Murphy" <[e-mail]> wrote in message
news:k4******** **********@news-server.bigpond. net.au...

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

I did a genealogy database myself, and for this I created two date fields
for each case - one as a text field that stores what is typed in, and a
second that converts this to the actual closest date for sorting and
comparison purposes. So "March 1900" is coverted over to 1/1/1900 in the
actual date field, but the application displays the text field.

Excellent solution...

--
Thanks

Murph