Storing date of birth in MySQL

Question

I have two options to store the DOB (date of birth) in MySQL. Which is the better method from the following?

Storing DOB using Date Type in MySQL,

OR

Storing DOB values separately in different fields like Day in one field, month in another and year in another?

If the client wants to display the month and date only, is it possible to retrieve it, if we store DOB in date type of MySQL?

Answer 1

I'd say always store a date as a DATE type. MySQL has a host of date functions that will calculate time spans, format the date however you'd like to see it, and extract pieces of the date for you.

As an example, if you need the month only, just do MONTH(DOB).

If you need more convincing, take a look around StackOverflow and you'll see hundreds (if not thousands) of questions from folks who are having trouble because their date values are stored as strings.

Answer 2

Use a Date.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

You can query using some Date Functions:

SELECT * 
FROM table 
WHERE 
   MONTH(date_column) = desired_month AND 
   DAY(date_column) = desired_day

Answer 3

Date logic is tricky. If you can use a built-in type that's already been debugged, you should use it. Retrieving the month from a date column is fairly straightfoward.