What is the best field to store the birthday?

Question

I have to store in my mysql database, the users's birthday ( like 12-11-1990 ).

What field type do I have to use to store it ?

Answer 1

to store a date, you should probably use a date. to quote the documentation:

The DATE type is used for values with a date part but no time part.

Answer 2

You can store it as a DATE object as you would normally do with non-repeating dates. The, if you're using MySQL (I believe other DBMS also have functions for this) you can retrieve birthdays using MySQL functions as described here:

SELECT *  FROM table_name  WHERE      MONTH(date_field) = desired_month AND      DAY(date_field) = desired_day 

This method can be a bit slow when dealing with thousands of records. If that's your case, you can store the birthday with 3 separate INTs, one for each (year, month, day). Then you search birthdays like this:

SELECT * FROM table_name WHERE    month_field = desired_month AND    day_field = desired_day 

If you use this last method, I'd advise you to also store a DATETIME version of the birthday so that you can build the date without any kind of maneuvers. You can additionally index each one of the 3 fields (non-unique) so it's faster to retrieve them.