Storing day and month (without year)

I’ve got to store DD/MM dates in a database, but I’m not sure of the best way to store this so that it can be easily sorted and searched.

The best way to store date data, even if the year component is not required, is to use date. When you need to use it, you can remove the year, or replace it with the year being compared against (or current year).

Having it in date column facilitates sorting correctly, integrity, validation etc.

To cater for leap years, use a year like ‘0004’ which allows ‘0004-02-29′. Using year 4 makes it slightly more complicated than year 0, but as an example, this turns the date ’29-Feb’ (year agnostic) into a date in this year for comparison with some other field

select
    adddate(
    subdate(cast('0004-02-29' as date),
        interval 4 year),
        interval year(curdate()) year)

result: 2011-02-28