CODEWITHSUNDEEP
mysqldatabase-design
Sasha
Sasha

Reputation: 2277

Storing day and month (without year)

I'm having trouble with figuring out the best way to store some data in my database. 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.

Basically a user will be able to save important dates in the format DD/MM, which they will be reminded of closer to the day.

The DATE data type doesn't seem completely appropriate as it includes year, but I can't think of another way of storing this data. It would be possible to include a specific year to the end of all occasions, but this almost doesn't seem right.

Upvotes: 34

Views: 22984

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

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

Upvotes: 53

shamittomar
shamittomar

Reputation: 46692

If you really really want to drop the year, then just make TWO columns, one for day and another for month. Then store them separately.

CREATE TABLE `table-name` (
  `Day` tinyint NOT NULL,
  `Month` tinyint NOT NULL
);

But, it's much better to just use the Date type and then ignore the year in your code.

Upvotes: 5

ClosureCowboy
ClosureCowboy

Reputation: 21531

Are these dates recurring? If not, how will you keep track of when one has "expired"? If the answer is "the app will manually remove the dates once they have expired", then why not simply store the DD/MM date as the next available instance of that date? For example:

01/02 becomes 2012-02-01, and 04\07 becomes 2011-07-04

The built-in date/time functions are so useful that I strongly recommend you not use varchars or tinyints.

Upvotes: 6

Related Questions